【mysql】mysql使用结果集删除记录

mysql使用结果集删除记录

需求: 删除T表中的信息，但是需要删除的ID是通过A、B、C三张表联合查询出来的。

第一次编写的sql

DELETE from t_activity_class_cycle
WHERE
cmy_a_c_c_id IN (
    SELECT
        a.cmy_a_c_c_id
    FROM
        t_activity_class_cycle a
    LEFT JOIN t_activity_class b ON a.cmy_a_c_id = b.cmy_a_c_id
    LEFT JOIN t_community c ON b.cmy_id = c.cmy_id
    WHERE
        a.state = 1
    AND b.state = 1
    AND c.state = 1
    AND c.school_id = 17
)

结果报错了

You can’t specify target table ‘t_activity_class_cycle’ for update in FROM clause

您无法在FROM子句中指定目标表“ t_activity_class_cycle”进行更新

正确的写法是将结果集再select出来形成一张虚拟表， in 才能使用

正确的写法

DELETE
FROM
    t_activity_class_cycle
WHERE
    cmy_a_c_c_id IN (
        SELECT
            *
        FROM
            (
                SELECT
                    a.cmy_a_c_c_id
                FROM
                    t_activity_class_cycle a
                LEFT JOIN t_activity_class b ON a.cmy_a_c_id = b.cmy_a_c_id
                LEFT JOIN t_community c ON b.cmy_id = c.cmy_id
                WHERE
                    a.state = 1
                AND b.state = 1
                AND c.state = 1
                AND c.school_id = 17
            ) a
    )

这个问题只有在mysql中才会出现