Update Query With Aggregate

jltromb

New Member
Joined
Mar 31, 2017
Messages
5
All - I currently have this query (which works fine):
Code:
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!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
Possibly like this:
Code:
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
			)
 
Upvote 0
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.
 
Upvote 0
Okay. That's always a reasonable workaround, and sometimes you will get best performance anyway.
 
Upvote 0
Yeah, unfortunately, it's not the workaround that I wanted. I have about 15 tables that will require 'workarounds' :(
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top