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)
 UPDATE with Inner Join and SUM

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 SD
SET SbilPiu = SUM(RdP.SbilCond)
FROM app.T_SbilDelta SD
INNER JOIN app.T_RdPOrario RdP
ON rdp.RdPOrarioTestata_ID = sd.RapportoDiProduzioneTestata_ID
WHERE rdp.SbilCond > 0 AND sd.RapportoDiProduzioneTestata_ID = 37

In 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.
Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-02-26 : 08:22:24
Well done, thank you very much Webfred.

Luigi
Go to Top of Page

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 shot

update d
set d.SbilPiu = o.Total
FROM T_SbilDelta d
INNER JOIN (select RapportoDiProduzioneTestata_ID,sum(SbilCond) AS Total
from T_RdPOrario
group by RapportoDiProduzioneTestata_ID)o
ON o.RapportoDiProduzioneTestata_ID = d.RapportoDiProduzioneTestata_ID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-02-27 : 02:33:59
Good example, I'll keep it. Thank you Visakh.

Luigi
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 04:10:48
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 d
set d.SbilPiu = o.Total
FROM T_SbilDelta d
INNER JOIN cte o
ON o.RapportoDiProduzioneTestata_ID = d.RapportoDiProduzioneTestata_ID


djj
Go to Top of Page

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 = ...


Go to Top of Page
   

- Advertisement -