Bug #42615 UPDATE statement workflow
Submitted: 5 Feb 2009 10:59 Modified: 22 Feb 2009 7:47
Reporter: Samuel Grznar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.0.27 OS:FreeBSD
Assigned to:
Tags: UPDATE, workflow
Triage: Needs Triage: D5 (Feature request)

[5 Feb 2009 10:59] Samuel Grznar
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}
[22 Feb 2009 7:47] Valerii Kravchuk
Thank you for a reasonable feature request.