update syntax

Database

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 (1)
3 REPLIES
Enthusiast

Re: update syntax

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

Re: update syntax

Thx for the help.

Junior Contributor

Re: update syntax

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