MySQL按列值删除多行报无法在FROM子句指定更新目标表错误

MySQL存在明确的语句执行限制：执行DELETE、UPDATE这类修改表数据的操作时，不能在WHERE条件的子查询中直接读取正在被修改的同一张目标表。
你当前的语句中，DELETE操作的目标表是tableAA，但IN子查询里直接执行了SELECT id FROM tableAA读取同一张表，数据库无法同时对同一张表加写锁和读锁完成子查询判断，就会抛出该报错。

下面两种方案均可直接解决问题，可根据业务场景选择：

• 方法1：嵌套一层派生表绕开同表引用限制
  这是兼容性最好的通用方案，所有MySQL 5.7+版本均可直接使用。只需要把查询同表的子查询再套一层外层SELECT，让MySQL先把符合条件的id结果存入临时派生表，再基于临时表的结果执行删除，即可绕开同表引用的限制：

  DELETE FROM tableAA 
  WHERE id IN (
      SELECT temp.id FROM (
          SELECT id FROM tableAA 
          WHERE coreID IN (SELECT fill_ID FROM tableBBB) 
          AND columnData = 'data'
      ) AS temp
  );
  

• 方法2：改用多表连接删除语法（大数据量优先选择）
  这种写法不需要嵌套多层子查询，通过表关联直接匹配要删除的行，执行效率远高于多层IN子查询，也不会触发同表引用的限制：

  DELETE t1 
  FROM tableAA t1
  INNER JOIN tableAA t2 ON t1.id = t2.id
  INNER JOIN tableBBB b ON t2.coreID = b.fill_ID
  WHERE t2.columnData = 'data';
  

注意：执行删除操作前建议先把语句开头的DELETE换成SELECT * 执行一遍，确认匹配到的行都是目标删除数据，避免误删。

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