WL#927: Simultaneous assignments in UPDATE ... SET

 
If an UPDATE statement has more than one 
assignment in its SET clause, they are all 
performed effectively simultaneously. This 
affects only SET clauses where the same 
column name appears both as a source and 
as a target in the assignments. 
 
CREATE TABLE E101_03B (S1 CHAR, S2 CHAR) 
INSERT INTO E101_03B VALUES ('X', '') 
UPDATE E101_03B SET S1 = S2, S2 = S1 
SELECT * FROM E101_03B 
 
The result of the above selection should be: 
'', 'X' 
 
This is required for compliance with standard 
feature ID E01-03, "Searched UPDATE statements". 

This task is mentioned in Matthias Leich's email in dev-public archive
"Re: REPORT: NIST tests on MySQL 5.0 2005-04-04"

Feature requests:
BUG#13943 update tbl set fld1=fld2, fld2=fld1 fails to switch values
BUG#42615 UPDATE statement workflow
BUG#52861 Non-atomic UPDATE on single row of single table (order of evaluation)

One issue that has to be resolved is whether implementation of this task should
affect multi-table updates. One opinion is that it should not and that a
separate task should be created for multi-table updates. The rationale for this
is simply in the fact that changing multi-table updates to perform updates
simultaneously is  a much greater task thin in a single-table updates. Another
reason is that there are no SQL standards governing multi-table DML's.