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.
Copyright (c) 2000, 2013, Oracle Corporation and/or its affiliates. All rights reserved.
© 2013, Oracle Corporation and/or its affiliates
