Updating with a Select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Updating with a Select statement

Author

Topic

Hakuzen
Starting Member

24 Posts

Posted - 2009-07-08 : 15:40:53

Hello, I currently have an update statement that reads

		Update #comparetemp
			Set #comparetemp.MaxID = #MultiTicket.IDTSDetail
				from #Multiticket inner join #CompareTemp on #CompareTemp.FSEID = #multiticket.FSEID
			Where (#MultiTicket.IDTSDetail < #comparetemp.MaxID and @StartDate <= #multiticket.DateEstDeparture) 
				and (#multiticket.DateEstDeparture < #comparetemp.DateCustomerConfirm and #CompareTemp.FSEID = #multiticket.FSEID)

This statement works, but I need to change one of the WHERE clauses to include an expression with an AGGREGATE, something like

		Update #comparetemp
			Set #comparetemp.MaxID = #MultiTicket.IDTSDetail
				from #Multiticket inner join #CompareTemp on #CompareTemp.FSEID = #multiticket.FSEID
			Where (max(#MultiTicket.IDTSDetail) < #comparetemp.MaxID and @StartDate <= #multiticket.DateEstDeparture) 
				and (#multiticket.DateEstDeparture < #comparetemp.DateCustomerConfirm and #CompareTemp.FSEID = #multiticket.FSEID)

This does not work because you cannot have aggregates in WHERE clause, only HAVING clause and HAVING clause are only usable in a select statement as far as I know.

My question is, can I make a SELECT statement that does the same thing as my update statement above? Or is there any other way to acomplish what I'm trying to do?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 10:45:27

if you need to use max() you need to apply grouping over some field. which field you want to group on?