updating multiple columns of a sql server row in a single statement

Question

Hi I have a temp table as shown below.

create table #Temp_Test
(
id int,
prod int,
total int
)

insert into #Temp_Test (id,prod,total) 
values(1,10,20), (2,30,40), (3, 50,60)

I want to update id & prod in a single statement For example I want to update prod & total as 30 & 60 where id = 1.I am using the below query to do it :

update  #Temp_Test
set prod = 30 , total = prod*2
where id = 1

The above query gives updates my prod as 30 & total as 20. instead of 60 which I expect. (Prod*2 is just an example, I could have different calculations here Prod*32 , Prod*7 etc).

what changes I need to do for it to work (I want to do it using a single query, I know how to make it work using multiple update queries) ?

Perhaps you can define not working? There are no syntax errors here. — Sean Lange
– Sean Lange, Commented Nov 16, 2015 at 15:37
The update works on the table values before update, i.e. prod = 10 will be used when you set total. — jarlh
– jarlh, Commented Nov 16, 2015 at 15:37
By the way: Why do you store a value in a column ("total") that can be computed from another column ("prod*2") ? — Martin
– Martin, Commented Nov 16, 2015 at 16:35

AntDC · Accepted Answer · 2015-11-16 16:20:16Z

2

For the sake of making things nice and clear for the next guy who comes along to work on it.....

DECLARE @NewProd Int = 30
DECLARE @Qty Int = 2

UPDATE #Temp_Test SET 
  prod = @NewProd, 
  total = @Qty * @NewProd
WHERE 
  id = 1

answered Nov 16, 2015 at 16:20

AntDC

1,93715 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simone · Accepted Answer · 2015-11-16 15:37:18Z

0

Since you have 30 & 60 use those values:

update  #Temp_Test
set prod = 30 , total = 60
where id = 1

answered Nov 16, 2015 at 15:37

Simone

1,9441 gold badge14 silver badges22 bronze badges

Comments

Henrik supports the community · Accepted Answer · 2015-11-16 15:56:33Z

0

The SQL stanard is not freely available, but as far as I have understood it is slightly unclear on this, but it is by far easier to interpret it as saying that the old values should be used for evaluating all expression in the statement.

So if what you mean by "not working" is that the result is 'prod=30, total=40' that is the correct result. If you need to calculate one column based on the new value of another, either do it in the layer above (your code that connects to the db) or do it in two queries, wrapped in a transaction.

answered Nov 16, 2015 at 15:56

Henrik supports the community

8191 gold badge8 silver badges17 bronze badges

Collectives™ on Stack Overflow

updating multiple columns of a sql server row in a single statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related