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 Join and Sum
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 03/28/2013 :  04:34:48  Show Profile  Reply with Quote
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)

Singapore
17607 Posts

Posted - 03/28/2013 :  04:47:33  Show Profile  Reply with Quote
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 ;



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/28/2013 :  05:10:21  Show Profile  Reply with Quote
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

Italy
232 Posts

Posted - 03/28/2013 :  05:31:10  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 03/28/2013 :  05:33:50  Show Profile  Reply with Quote
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

Italy
232 Posts

Posted - 03/28/2013 :  05:48:33  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 03/28/2013 :  05:55:12  Show Profile  Reply with Quote
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

Italy
232 Posts

Posted - 03/28/2013 :  06:50:28  Show Profile  Reply with Quote
Sure, thank you Visakh.

Luigi
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.08 seconds. Powered By: Snitz Forums 2000