SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 UPDATE with Inner Join and SUM		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
176 Posts
Posted - 02/26/2013 :  06:36:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8513 Posts
Posted - 02/26/2013 :  06:54:46  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Edited by - webfred on 02/26/2013 06:55:29
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
176 Posts
Posted - 02/26/2013 :  08:22:24  Show Profile  Reply with Quote
Well done, thank you very much Webfred.

Luigi
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 02/26/2013 :  22:47:36  Show Profile  Reply with Quote
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

Italy
176 Posts
Posted - 02/27/2013 :  02:33:59  Show Profile  Reply with Quote
Good example, I'll keep it. Thank you Visakh.

Luigi
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 02/27/2013 :  04:10:48  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

djj55
Yak Posting Veteran

USA
84 Posts
Posted - 02/27/2013 :  15:50:43  Show Profile  Reply with Quote
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
Yak Posting Veteran

USA
78 Posts
Posted - 02/27/2013 :  17:24:04  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000