sql - 使用 CASE 语句的嵌套 SQL 查询

Question

如何只编写一个 SQL 查询来通过执行这两个查询(步骤)获得相同的结果:

第一步:

SELECT old_id, new_id FROM history WHERE flag = 1;

结果:

+--------+--------+
| old_id | new_id |
+--------+--------+
|     11 |     22 |
|     33 |     44 |
|     55 |     66 |
+--------+--------+

然后，使用以前的结果，执行此查询:

UPDATE other_tabla SET somefk_id = CASE somefk_id
    WHEN 11 THEN 22
    WHEN 33 THEN 44
    WHEN 55 THEN 66
END WHERE somefk_id IN (11,33,55)

Answer 1

我认为这就是您所描述的:

UPDATE `other_tablea`
JOIN `history` ON history.old_id = other_tablea.somefk_id AND history.flag = 1
SET other_tablea.somefk_id = history.new_id