mysql - 如何根据其他表的数据在sql中查询更新

Question

我有 2 个表。例如:

table1
ID, data_static1, name1
1      8          Muna
2      1          Andi
3      7          null

table2
ID, data_static2, name2
1       0         Aji
2       1         Andi
3       2         max
4       3         nadine
5       4         Rio
6       5         Panji
7       6         Eko
8       7         Pan
9       8         Muna

我想根据 table1 中的最大 ID 更新 table1 中的 name1 列，其中 table1.data_static1 与 相同>table2.data_static2.

我想要如下结果

table1
ID, data_static1, name
1      8          Muna
2      1          Andi
3      7          Pan

我试过下面的代码

mysql> UPDATE theDB.table1 SET name1=(SELECT name2 FROM table2 WHERE data_static2=(SELECT data_static1 From table1 WHERE ID IN(SELECT MAX(ID) FROM table1))) WHERE table1.ID IN(SELECT MAX(table1.ID) FROM theDB.table1);

我收到一条错误消息

ERROR 1093 (HY000): You can't specify target table 'table1' for update in FROM clause

Answer 1

最简单的解决方案是为此使用相关子查询:

update table1 t1
set name1 = (
        select name2
        from table2 t2
        where t1.data_static1 = t2.data_static2
        order by id desc limit 1
        );

您也可以使用 JOIN:

update table1 t1
join (
    select *
    from table2 t
    join (
        select data_static2,
            max(id) as id
        from table2
        group by data_static2
        ) t2 using (data_static2, id)
    ) t2 on t1.data_static1 = t2.data_static2
set t1.name1 = t2.name2;