Description:
When updating multiple columns in one table, it seems that columns are updated immediately in the order they are written in update statement. This is quite bit confusing approach, when you want to update values dependent on the previous values of the columns.
You have no chance to get old values, unless you store it in some user variables. This is really crazy, because it could be simple update BUT you have to make procedure or multiple queries.
TO COMPARE APPROACHES:
1. MSSQL: it is easy to compute new values of some column (column.oldvalue + newvalue)
2. MySQL: you cant get old value of column1, if you have updated it already and wanna use this value to compute value in column2
Example:
WORKS WRONG:
UPDATE mytable
SET
col1 = col1 + 1,
col2 = ((col1 * col2) + (1 * 1)) / (col1 + col2)
WORKS RIGHT (just coz we dont need col2 value in second update):
UPDATE mytable
SET
col2 = ((col1 * col2) + (1 * 1)) / (col1 + col2),
col1 = col1 + 1
There can be many situation where just changing order doesnt help coz of need of old values of columns to compute new values.
>>> Old posts with the same issue
RE: [name withheld] on March 21 2006 3:05am
RE: Sadder But Wiser on February 8 2006 5:11pm
create table a (id int, v1 float, v2 float);
insert into a values(1,1,2);
update a SET v1=v1/(v1+v2), v2=v1/(v1+v2);
select * from a;
Actual Result:* 1 0.333333 0.142857
Expected Result: 1 0.333333 0.333333
This works exactly to the specifications of the documentation. the value of v1 is changed to 0.333333 before the equation to update v2 is evaluated, so the expected result is 0.142857
How to repeat:
UPDATE mytable
SET
col1 = col1 + 1,
col2 = ((col1 * col2) + (1 * 1)) / (col1 + col2)
Suggested fix:
add optional possibility to update multiple columns with no immediate update of columns as they are written in the update statement. {transaction like}
Description: When updating multiple columns in one table, it seems that columns are updated immediately in the order they are written in update statement. This is quite bit confusing approach, when you want to update values dependent on the previous values of the columns. You have no chance to get old values, unless you store it in some user variables. This is really crazy, because it could be simple update BUT you have to make procedure or multiple queries. TO COMPARE APPROACHES: 1. MSSQL: it is easy to compute new values of some column (column.oldvalue + newvalue) 2. MySQL: you cant get old value of column1, if you have updated it already and wanna use this value to compute value in column2 Example: WORKS WRONG: UPDATE mytable SET col1 = col1 + 1, col2 = ((col1 * col2) + (1 * 1)) / (col1 + col2) WORKS RIGHT (just coz we dont need col2 value in second update): UPDATE mytable SET col2 = ((col1 * col2) + (1 * 1)) / (col1 + col2), col1 = col1 + 1 There can be many situation where just changing order doesnt help coz of need of old values of columns to compute new values. >>> Old posts with the same issue RE: [name withheld] on March 21 2006 3:05am RE: Sadder But Wiser on February 8 2006 5:11pm create table a (id int, v1 float, v2 float); insert into a values(1,1,2); update a SET v1=v1/(v1+v2), v2=v1/(v1+v2); select * from a; Actual Result:* 1 0.333333 0.142857 Expected Result: 1 0.333333 0.333333 This works exactly to the specifications of the documentation. the value of v1 is changed to 0.333333 before the equation to update v2 is evaluated, so the expected result is 0.142857 How to repeat: UPDATE mytable SET col1 = col1 + 1, col2 = ((col1 * col2) + (1 * 1)) / (col1 + col2) Suggested fix: add optional possibility to update multiple columns with no immediate update of columns as they are written in the update statement. {transaction like}