Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-27-2015
12:11 AM

02-27-2015
12:11 AM

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.

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-27-2015
12:25 AM

02-27-2015
12:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-27-2015
12:29 AM

02-27-2015
12:29 AM

Re: update syntax

Thx for the help.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-27-2015
04:04 AM

02-27-2015
04:04 AM

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