WL#927: Simultaneous assignments in UPDATE ... SET
Affects: Server-7.1 — Status: Un-Assigned
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, 2022, Oracle Corporation and/or its affiliates. All rights reserved.