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 Join and Sum

Author  Topic 

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-28 : 04:34:48
Hello all,
I'm trying this Update:

UPDATE SD
SET SbilOnerPiu = (SELECT ISNULL(SUM(RO.SbilOner),0)
FROM app.T_SbilDelta SD
INNER JOIN app.T_RdPOrario RO
ON SD.RapportoDiProduzioneTestata_ID = RO.RdPOrarioTestata_ID
WHERE SD.RapportoDiProduzioneTestata_ID = @RdPOrarioTestataID
AND SD.TipoAbilitazione_ID = 7 AND RO.SbilOner >= 0);

but I'm getting the error:

Invalid object name 'SD'.

What's wrong with this?

Luigi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-03-28 : 04:47:33
[code]UPDATE SD
SET SbilOnerPiu = ( SELECT ISNULL(SUM(RO.SbilOner),0)
FROM app.T_RdPOrario RO
WHERE SD.RapportoDiProduzioneTestata_ID = RO.RdPOrarioTestata_ID
AND RO.SbilOner >= 0 )
FROM app.T_SbilDelta SD
WHERE SD.RapportoDiProduzioneTestata_ID = @RdPOrarioTestataID
AND SD.TipoAbilitazione_ID = 7 ;[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-28 : 05:10:21
can you elaborate with some sample data what you're trying to update?

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

Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-28 : 05:31:10
The Khtan's solution works well, thank you very much.
So I need to have the SUM aggregation in a subquery.

Luigi
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-28 : 05:33:50
this can still return NULL if you dont have any matching records returned by subquery

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

Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-28 : 05:48:33
Ok, I can accept that. In normal way there always a matching records.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-28 : 05:55:12
ok.. otherwise you've wrap the subquery around ISNULL or COALESCE to convert them to some constant value

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

Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-28 : 06:50:28
Sure, thank you Visakh.

Luigi
Go to Top of Page
   

- Advertisement -