Question 112704 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How can I rephrase this UPDATE INNER JOIN query such that the columns of t1 are accessible in the subquery?

On running the following MySQL (5.7.12) 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
INNER JOIN (
    SELECT col1, col2
    FROM MySecondTable
    WHERE col2 < t1.col2
) AS t3
ON t1.col1 = t3.col1 AND t3.col2 = t2.col2
SET t2.col3 = t1.col3;

The objective here is to update MyFirstTable with rows from MySecondTable where, for a given col1 value, col2 was both less than and more than t1.col2 (on different rows, of course).

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search