All Forums Database
kevin4423 3 posts Joined 02/12
27 Feb 2015
update syntax

table_1
(
c1 int
,c2 int
,c3 int
);

insert into table_1 values (1,2,3);

update table_1
set c2 = c2 + 5 --c2's value suppose to be 7 now
,c3 = c2 + c1; --c3's value suppose to be 7 + 1 = 8 now

select c1, c2, c3 from table_1;

result: 1, 7, 3;
Result supposed to be: 1, 7, 8.
But c3's value is still 3, no change has been made.
 Any suggest.
Sorry for my English.

Tags:
Rohan_Sawant 55 posts Joined 07/14
27 Feb 2015

Hi Kevin,
 
Whenever a update happens it takes the present value of the table. In your update the present value of c2 is '2' and hence '2' would be used in both the SET OF UPDATE. So c3 = c2(present value) + c1(present value) = 2 + 1 = 3. Its not a line based processing where the first SET would run and the the second SET. Both works together. For your requirement you need to use 2 UPDATE's.
 

UPDATE table_1
SET c2 = c2 + 5 --c2's value suppose to be 7 now

UPDATE table_1
SET c3 = c2 + c1; --c3's value suppose to be 7 + 1 = 8 now

 
Thanks,
Rohan Sawant

kevin4423 3 posts Joined 02/12
27 Feb 2015

Thx for the help.

dnoeth 4628 posts Joined 11/04
27 Feb 2015

Hi Kevin,
you might have been exposed to MySQL :)
MySQL does it the way you described, but every other DBMS does it like Teradata, the order within the SET is irrelevant.
 
Instead of two updates you could also combine them into one by duplicating the calculation:

UPDATE table_1
 SET c2 = c2 + 5 --c2's value suppose to be 7 now
,SET c3 = c2 + 5 + c1; --c3's value suppose to be 7 + 1 = 8 now

 

Dieter

You must sign in to leave a comment.