本文共 1410 字，大约阅读时间需要 4 分钟。

今天被下面的SQL搞了很久，总是被you can't specify target table for update in from clause。

刚开始我是如下写法：

UPDATE xrshop.product AS aaaINNER JOIN (    SELECT we.sn, we.productId FROM warehouse.examination we WHERE we.productId IN (        SELECT p.id FROM xrshop.product AS p WHERE CODE IS NULL AND p.majorCategoryId = 17        ... 省略一些条件    )) AS b SET aaa.`code` = b.sn WHERE b.productId = aaa.id

这样总是报错：后来我改为：

UPDATE xrshop.product AS aaaINNER JOIN (select sn, productId from (SELECT we.sn, we.productId FROM warehouse.examination we WHERE we.productId IN (    SELECT p.id FROM xrshop.product AS p WHERE CODE IS NULL AND p.majorCategoryId = 17 AND p.minorCategoryId IN (        17005, 17009, 17012, 17013, 17010, 17011, 17015    )))) AS b ) bbbSET aaa.`code` = bbb.sn WHERE bbb.productId = aaa.id

也就是对INNER JOIN后面的查询结果集，再用select包一下，然后就可以了；

同样地，在删除操作中，如果是删除表A的数据，而数据来源于表A的查询结果，也需要使用select包裹：

DELETE from xrshop.product WHERE id in (select a.id from (SELECT p.id FROM xrshop.product AS p WHERE CODE IS NULL AND p.majorCategoryId = 17 AND p.minorCategoryId IN (    17005, 17009, 17012, 17013, 17010, 17011, 17015)) as a)

至于not in优化问题，因为not in不走索引，所以在不得已情况下，就不要使用not in做查询，下面使用join来替代not in。

比如：

select ID from A where ID not in (select ID from B)

替换为：

select A.ID from A left join B on A.ID=B.ID and B.ID is null

或者：

select A.ID from A left join B on A.ID=B.ID where B.ID is null

这种方法通过left join和where条件来实现not in的效果，同时避免了不使用索引的情况。