mysql - 将临时表中的内容插入到更新查询中

Question

我想做的是通过从临时表中获取的值更新表。

请参阅下面的布局以进行说明:

我想要id = 1的用户名“test”，从临时表中更改为new_id = 2的值。

Temporary table:

---------------------------------------------------------
|   username    |   old_id  |   new_id  |   directory   |
---------------------------------------------------------
|   test        |   1       |   2       |   old         |
---------------------------------------------------------

Actual table:

-----------------------------------------
|   username    |   id  |   directory   |
-----------------------------------------
|   test        |   1   |   old         |
|   test        |   2   |   new         |
-----------------------------------------

这是我目前所拥有的，但它的失败非常严重:

DROP TABLE IF EXISTS TMPRESULT;

CREATE TEMPORARY TABLE IF NOT EXISTS TMPRESULT AS (
  SELECT 
  U.directory AS 'directory',
  U.username AS 'Username',
  U.ID AS 'old_id', 
  (
    SELECT 
    T1.id 
    FROM table1 AS T1
    WHERE U.username = T1.username
    AND T1.directory = 'new'
  ) AS 'new_id'
  FROM table1 AS U
  WHERE U.directory = 'old'
  AND U.username = 'test'
);

UPDATE table2 AS M
SET
  M.id = T.new_id
FROM table2
INNER JOIN TMPRESULT AS T ON M.child_id = T.old_id
WHERE M.username = T.Username

Answer 1

这是 MySQL 中的正确语法:

UPDATE table2 M INNER JOIN
       TMPRESULT T
       ON M.child_id = T.old_id AND M.username = T.Username
    SET M.id = T.new_id;