Can I do this in 1 statement? - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Can I do this in 1 statement?

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2010-07-16 : 13:31:04

He all,

I have the following which selects values into parameters from one table and then updates another table with those parameters.

Is there a way to skip the parameter part and just do 1 update statement? Any help is greatly appreciated.

Here is an example:


Declare @MemberID bigint
Set @MemberID = 119152

Declare @Score int
Declare @Bonuses int
Declare @Penalties int

Select 
	@Score = IsNull(Sum(score), 0)
	,@Bonuses = IsNull(Sum(bonuses), 0)
	,@penalties = IsNull(Sum(penalties), 0)
From dbo.member_score
Where memberid = @MemberID
And active = 1

Update dbo.members
Set 
	score = @Score
	,bonuses = @Bonuses
	,penalties = @Penalties
Where memberid = @MemberID

JBelthoff
› As far as myself... I do this for fun!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-16 : 13:34:23

this should do it. try it out on dev of course


Update dbo.members tgt
Set	Score = IsNull(Sum(score), 0)
	,Bonuses = IsNull(Sum(bonuses), 0)
	,penalties = IsNull(Sum(penalties), 0)
From dbo.member_score src
Where memberid = @MemberID
And active = 1

If you don't have the passion to help people, you have no passion

JBelthoff
Posting Yak Master

173 Posts

Posted - 2010-07-16 : 13:36:32

Nice thank you!

JBelthoff
› As far as myself... I do this for fun!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources