Mysql删除同一张表的子查询语句

最新推荐文章于 2021-02-07 15:36:49 发布

韭菜猫骑士

最新推荐文章于 2021-02-07 15:36:49 发布

阅读量3.7k

点赞数 4

本文链接：https://blog.csdn.net/hithedy/article/details/41827017

版权

DELETE
FROM
    TBL_NODE
WHERE
    parent_node_id IN
    (
       
                SELECT
                    node_id
                FROM
                    tbl_node
                WHERE
                    parent_node_id =12345)

原来的语句如上，在mysql执行会报错如下！

[DELETE - 0 row(s), 0.000 secs] [Error Code: 1093, SQL State: HY000] You can't specify target table 'TBL_NODE' for update in FROM clause
Code: 1093 SQL State: HY000 --- You can't specify target table 'TBL_NODE' for update in FROM clause
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

mysql不支持这种删除。修改为如下即可：

DELETE
FROM
    TBL_NODE
WHERE
    parent_node_id IN
    (
        SELECT
            *
        FROM
            (
                SELECT
                    node_id
                FROM
                    tbl_node
                WHERE
                    parent_node_id = 12345 ) AS temp )