使用child表左连parent表的结果更新child表state字段失败如何解决？

你的SQL逻辑本身没问题，目标是将child表中无法和parent表按sgl_id、school_id双字段匹配到的记录的state字段更新为1，但触发了主流数据库（如MySQL）的同表更新限制：不允许在UPDATE语句的子查询中直接引用正在被更新的目标表，会抛出类似You can't specify target table 'child' for update in FROM clause的报错。

方案1：子查询套临时表兼容旧逻辑 #

只需将原内层子查询的结果再包一层临时表别名，即可绕开同表引用限制：

UPDATE child
SET state = 1
WHERE sgl_id IN (
    SELECT tmp.sgl_id FROM (
        SELECT child.sgl_id
        FROM child
        LEFT JOIN parent ON child.sgl_id = parent.sgl_id
         AND child.school_id = parent.school_id
        WHERE parent.sgl_id IS NULL
    ) AS tmp
)

方案2：多表关联更新（更高效，推荐） #

直接用多表UPDATE语法实现需求，无需嵌套子查询，执行性能更优：

UPDATE child
LEFT JOIN parent 
    ON child.sgl_id = parent.sgl_id 
    AND child.school_id = parent.school_id
SET child.state = 1
WHERE parent.sgl_id IS NULL

内容的提问来源于stack exchange，提问作者user35181