Mysql删除语句提示： You can‘t specify target table ‘xxx‘ for update in FROM clause

最新推荐文章于 2024-01-03 11:14:48 发布

「已注销」

最新推荐文章于 2024-01-03 11:14:48 发布

阅读量279

点赞数

分类专栏：技术

本文链接：https://blog.csdn.net/kinris/article/details/119755818

版权

技术专栏收录该内容

3 篇文章 0 订阅

订阅专栏

今天写了一个删除语句，结果发现语法错误。

版本1：

DELETE
FROM
   m_doc_label
WHERE
   doc_id IN (
   SELECT
       DISTINCT doc_id
   FROM
       m_doc_label
   WHERE
       label_id IN (
       417757727196987392,
       417622660965482496,
       417622801545969664,
       417622883137765376 )
   AND doc_type = 3 )
AND label_id NOT IN (
   SELECT
       label_id
   FROM
       t_label
WHERE
   label_type_id IN (
   143087796171575296,
   349373250586820608,
   143088139458580480,
   193909373397897216,
   263825241917100032,
   229455442957307906,
   142083979829317632 ))

结果提示：You can't specify target table 'm_doc_label' for update in FROM clause

初步判断，要删除的表在条件中被引用估计不行。

版本2：

DELETE
FROM
   m_doc_label
WHERE
   doc_id IN (
   SELECT
       DISTINCT a.doc_id
   FROM
       m_doc_label  a
   WHERE
       a.label_id IN (
       417757727196987392,
       417622660965482496,
       417622801545969664,
       417622883137765376 )
   AND a.doc_type = 3 )
AND label_id NOT IN (
   SELECT
       label_id
   FROM
       t_label
WHERE
   label_type_id IN (
   143087796171575296,
   349373250586820608,
   143088139458580480,
   193909373397897216,
   263825241917100032,
   229455442957307906,
   142083979829317632 ))

给条件引用表加上别名，独立于删除表，发现还是不行。

版本3：

DELETE
FROM
   m_doc_label
WHERE
   doc_id IN (
   SELECT
   doc_id
   FROM (
       SELECT
           DISTINCT a.doc_id
       FROM
           m_doc_label  a
       WHERE
           a.label_id IN (
           417757727196987392,
           417622660965482496,
           417622801545969664,
           417622883137765376 )
       AND a.doc_type = 3 ) b)
AND label_id NOT IN (
   SELECT
       label_id
   FROM
       t_label
WHERE
   label_type_id IN (
   143087796171575296,
   349373250586820608,
   143088139458580480,
   193909373397897216,
   263825241917100032,
   229455442957307906,
   142083979829317632 ))