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.
Author |
Topic |
Ciupaz
Posting Yak Master
232 Posts |
Posted - 2013-02-26 : 06:36:47
|
Hello all,how can I adjust this query to perform this Update? UPDATE SDSET SbilPiu = SUM(RdP.SbilCond)FROM app.T_SbilDelta SDINNER JOIN app.T_RdPOrario RdP ON rdp.RdPOrarioTestata_ID = sd.RapportoDiProduzioneTestata_ID WHERE rdp.SbilCond > 0 AND sd.RapportoDiProduzioneTestata_ID = 37In this way it returns me this error: An aggregate may not appear in the set list of an UPDATE statement.Thanks in advance. Luigi |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-02-26 : 06:54:46
|
update T_SbilDelta set SbilPiu = (select sum(SbilCond) from T_RdPOrario where RapportoDiProduzioneTestata_ID = 37)where RapportoDiProduzioneTestata_ID = 37 Too old to Rock'n'Roll too young to die. |
|
|
Ciupaz
Posting Yak Master
232 Posts |
Posted - 2013-02-26 : 08:22:24
|
Well done, thank you very much Webfred. Luigi |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-26 : 22:47:36
|
make it generic to do update for all ID values in one shotupdate dset d.SbilPiu = o.TotalFROM T_SbilDelta dINNER JOIN (select RapportoDiProduzioneTestata_ID,sum(SbilCond) AS Total from T_RdPOrario group by RapportoDiProduzioneTestata_ID)oON o.RapportoDiProduzioneTestata_ID = d.RapportoDiProduzioneTestata_ID ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Ciupaz
Posting Yak Master
232 Posts |
Posted - 2013-02-27 : 02:33:59
|
Good example, I'll keep it. Thank you Visakh.Luigi |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-27 : 04:10:48
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-02-27 : 15:50:43
|
Could also use a cte;WITH cte AS (select RapportoDiProduzioneTestata_ID,sum(SbilCond) AS Total from T_RdPOrario group by RapportoDiProduzioneTestata_ID)update dset d.SbilPiu = o.TotalFROM T_SbilDelta dINNER JOIN cte oON o.RapportoDiProduzioneTestata_ID = d.RapportoDiProduzioneTestata_ID djj |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-02-27 : 17:24:04
|
You may need to add the other condition in the original query:UPDATE dbo.T_SbilDelta SET SbilPiu = (SELECT SUM(SbilCond) FROM dbo.T_RdPOrario ... WHERE ... AND SbilCond > 0)WHERE RapportoDiProduzioneTestata_ID = ... |
|
|
|
|
|