Update Query With Aggregate

[jltromb]

All - I currently have this query (which works fine):

UPDATE 
END_INSPECTION  AS e INNER JOIN qa33EndInspection_link AS q ON e.Batch = q.Batch AND e.Material = q.Material

SET  e.QI_End_Inspection = q.[End of Inspection] --(I want the MAX() of this!)

WHERE e.Batch = q.Batch AND e.Material = q.Material

What I really want though, is to be able to get the MAX(q.[End of Inspection]) because the q.Batch field has multiples of the same batch number with different dates [End of Inspection].

Thanks in advance!

 

[ranman256]

Normally you can't update using a Summation op.(max included)
you would have 2 queries,
Q1: select ID, max(field) from table

then join the Max query on the data...
Q2: select tData.* from tData,Q1 where tData.ID=Q1.ID

But it won't let you update. You have to write it to a temp table,then update from that.

 

[james_lankford]

and you don't need the "where" clause because you've already got the same thing in the inner join

 

[xenou]

Possibly like this:

UPDATE END_INSPECTION  e 
SET e.QI_End_Inspection = 
			(SELECT MAX(q.[End of Inspection]) 
				FROM qa33EndInspection_link q 
				WHERE q.Batch = e.Batch 
				AND q.Material = e.Material
			)

 

[jltromb]

I think ranman is right - it will take a temp table. xenou, I gave that a shot and I get the dreaded, "Must be an updateable query" message. Thanks guys.

 

[xenou]

Okay. That's always a reasonable workaround, and sometimes you will get best performance anyway.

 

[jltromb]

Yeah, unfortunately, it's not the workaround that I wanted. I have about 15 tables that will require 'workarounds'