May 28, 2009 at 8:13 am
The following query fails reporting 'An aggregate may not appear in the set list of an update statement'
update pallet set palllet.qcscore = max(score) from qcd inner join qch on qcd.qchid = qch.qchid
inner join qcprof on qch.qcprofid = qcprof.qcprofid
inner join qccase on qcprof.test = qccase.test
inner join pallet on qch.palletid = pallet.palletid
where qccase.test = 3 and qcd.qcnum = 41 and pallet.palletid = 369838
How can I get the update to work?
May 28, 2009 at 8:27 am
Use a subquery that does the aggregation.
update pallet set palllet.qcscore = MaxScore
FROM ( SELECT max(score) as MaxScore, palletid from qcd inner join qch on qcd.qchid = qch.qchid
inner join qcprof on qch.qcprofid = qcprof.qcprofid
inner join qccase on qcprof.test = qccase.test
WHERE qccase.test = 3 and qcd.qcnum = 41) sub
inner join pallet on sub.palletid = pallet.palletid
WHERE pallet.palletid = 369838
I can't test that without DDL and test data but even if it doesn't work it should give you the idea.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 28, 2009 at 8:44 am
Just a correction, need to add the group by clause to the derived subquery.
update pallet set palllet.qcscore = MaxScore
FROM ( SELECT max(score) as MaxScore, palletid from qcd inner join qch on qcd.qchid = qch.qchid
inner join qcprof on qch.qcprofid = qcprof.qcprofid
inner join qccase on qcprof.test = qccase.test
WHERE qccase.test = 3 and qcd.qcnum = 41
GROUP BY palletid) sub
inner join pallet on sub.palletid = pallet.palletid
WHERE pallet.palletid = 369838
--Ramesh
May 29, 2009 at 8:09 am
perfect, thank you so much.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply