How can I rephrase this UPDATE INNER JOIN query such that the columns of t1 are accessible in the su-CodePudding

On running the following MySQL UPDATE statement, I get the error "Error Code: 1054. Unknown column 't1.col2' in 'where clause'".

How can I rephrase this query such that the columns of t1 are accessible in the subquery?

UPDATE MyFirstTable AS t1
INNER JOIN (
    SELECT col1, col2
    FROM MySecondTable
    WHERE col2 > t1.col2
) AS t2
ON t1.col1 = t2.col1
SET col3 = t1.col3;

CodePudding user response：

You don't need a subquery here, simply make use of a multi-table update

Something like this should work:

UPDATE  MyFirstTable AS t1
JOIN    MySecondTable AS t2
ON      t1.col1 = t2.col1
SET     t2.col3 = t1.col3
WHERE   t2.col2 > t1.col2;

CodePudding user response：

Use LATERAL. See https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html

Demo:

mysql> select * from myfirsttable;
 ------ ------ ------ 
| col1 | col2 | col3 |
 ------ ------ ------ 
|    1 |    1 |    0 |
 ------ ------ ------ 
1 row in set (0.00 sec)

mysql> select * from mysecondtable;
 ------ ------ ------ 
| col1 | col2 | col3 |
 ------ ------ ------ 
|    1 |    2 |   42 |
 ------ ------ ------ 
1 row in set (0.00 sec)

As you discovered, a derived table cannot reference columns of other tables defined in the same FROM clause, which is standard SQL-92 behavior.

I made a couple of small changes to your query because it doesn't make sense the way you wrote it. Your query sets t1.col3 to itself, which doesn't accomplish anything. Also I added col3 to the derived table select-list, so it is available to the outer query.

mysql> UPDATE MyFirstTable AS t1 INNER JOIN (
  SELECT col1, col2, col3
  FROM MySecondTable 
  WHERE col2 > t1.col2 
) AS t2 ON t1.col1 = t2.col1 
SET t1.col3 = t2.col3;
ERROR 1054 (42S22): Unknown column 't1.col2' in 'where clause'

The new feature in SQL:1999 was to make this legal if the derived table is preceded by the LATERAL keyword:

mysql> UPDATE MyFirstTable AS t1 INNER JOIN LATERAL (
  SELECT col1, col2, col3
  FROM MySecondTable
  WHERE col2 > t1.col2
) AS t2 ON t1.col1 = t2.col1 
SET t1.col3 = t2.col3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

With this change, the value of col3 is copied from t2 to t1:

mysql> select * from myfirsttable;                                                                                     
 ------ ------ ------ 
| col1 | col2 | col3 |
 ------ ------ ------ 
|    1 |    1 |   42 |
 ------ ------ ------ 
1 row in set (0.00 sec)

Note that support in MySQL for the LATERAL feature was implemented in MySQL 8.0.14. If you use an older version of MySQL, you must upgrade to get this feature.