CODEWITHSUNDEEP
sql-serversetdeclare
user3770093
user3770093

Reputation: 355

SET inside UPDATE Statement in MSSQL not working

This is the query

DECLARE @tempvarA INT = 0
UPDATE Table1 SET @tempvarA = 1 + intColumn, columnA = @tempvarA

*intColumn is a column from Table1 It successfully executes the query but the value of columnA is 0 wherein @tempvarA was initially declared. Am I missing something here or does the value of @tempvarA only updates after the execution of the update statement?

I have tried the following:

DECLARE @tempvarA INT = 0
UPDATE Table1 SET @tempvarA = 1 + intColumn
UPDATE Table1 SET columnA = @tempvarA

It works but somehow I think there should be a more proper way to do it.

Upvotes: 1

Views: 1409

Answers (2)

A_Sk
A_Sk

Reputation: 4630

For

DECLARE @tempvarA INT = 0
UPDATE Table1 SET @tempvarA = 1, columnA = @tempvarA

You can try

DECLARE @tempvarA INT = 0
UPDATE t SET @tempvarA = 1, t.columnA = @tempvarA from My_Table_Name t

This Works fine for me.

Upvotes: 1

S.Karras
S.Karras

Reputation: 1493

So if I understand your question correctly, first you want to declare a variable and set it with a value and then update the value of a column with that variable. Let's take it step by step:

1)Declaring the variable:

DECLARE @tempvarA INT;

2)Setting the variable:

SET @tempvarA = 1;

3)Updating table with said variable:

UPDATE Table1 SET columnA = @tempvar1;

Just be careful though. The above will update ALL the values of columnA to that of tempvarA. If you want specific rows you will have to add a where clause in your UPDATE statement.

Upvotes: 4

Related Questions