Bug #70314 Ambiguous column updated with UPDATE ... JOIN ... USING

• Submitted: 12 Sep 2013 12:28
• Modified: 12 Sep 2013 12:51
• Reporter: Roy Lyseng
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.6
• OS: Any
• CPU Architecture: Any

[12 Sep 2013 12:28] Roy Lyseng

Description:
When updating based on two joined tables, it is possible to update a column which is also a USING column of the join, as in this statement:

  UPDATE t1 JOIN t2 USING (a) SET a=100;

In this statement, column "a" is the result of the join and cannot be said to be either t1.a or t2.a. However, the statement action is to update column t1.a. This is non-intuitive and is not documented. I propose that instead of the update, an error message should be given instead:

  ERROR XXXX (23000): Column 'a' in SET clause is ambiguous

As multi-table UPDATE is a MySQL extension over standard SQL, we cannot seek advice from the standard.

How to repeat:
CREATE TABLE t1(a INTEGER PRIMARY KEY);
CREATE TABLE t2(a INTEGER PRIMARY KEY);
INSERT INTO t1 VALUES (11),(12);
INSERT INTO t2 VALUES (10),(12);

SELECT * FROM t1 JOIN t2 USING (a);

UPDATE t1 JOIN t2 USING (a) SET a=100;

[12 Sep 2013 12:51] MySQL Verification Team

Thank you for the bug report.