CODEWITHSUNDEEP
sqlsql-server
Marek
Marek

Reputation: 3575

Declare variable in first statement then set as parameter in second

I'm trying to execute two lines of code in my SQL Server 2012:

SELECT SUM(paid) AS sumpaid FROM clipaid WHERE event='010101-01' AND year=2014;
UPDATE event SET totalpaid = sumpaid WHERE event='010101-01' AND year=2014

In the first line I would like to declare variable sumpaid and update this variable as parameter in second statemnt.

I'm getting this error: Invalid column name 'sumpaid'

Is there any way to achieve that?

Thanks for your time and suggestions.

Upvotes: 0

Views: 45

Answers (2)

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62498

you need to declare a variable like this:

declare @varSumPaid int

and the assign it value like this:

SELECT @varSumPaid = SUM(paid) 
FROM clipaid 
WHERE event='010101-01' 
AND year=2014;

and in update query use it like this:

 UPDATE event 
 SET totalpaid = @varSumPaid 
 WHERE event='010101-01' 
 AND year=2014;

Upvotes: 1

juergen d
juergen d

Reputation: 204904

You need to declare a variable

declare @sumpaid int
SELECT @sumpaid = SUM(paid) FROM clipaid WHERE event='010101-01' AND year=2014;
UPDATE event SET totalpaid = @sumpaid WHERE event='010101-01' AND year=2014

Upvotes: 3

Related Questions