Mysql 修改或者删除一个表的条件如果是 这个表的子查询,可能会出现下面这个错误:
You can't specify target table for update in FROM clause
这个意思是不能查询这个表的同时修改这个表的信息。
解决方法是在子查询的外部,在套一层查询。
例如
DELETE
FROM
test
WHERE
id IN (
SELECT
w2.id AS id
FROM
test AS w1
INNER JOIN test AS w2 ON w1.id < w2.id
AND w1.content = w2.content
);
这样会出错。
应该改成下面这样
DELETE
FROM
test
WHERE
id IN (
SELECT
a.id
FROM
(
SELECT
w2.id AS id
FROM
test AS w1
INNER JOIN test AS w2 ON w1.id < w2.id
AND w1.content = w2.content
) as a
);