delete mysql repeat recordset
删除mysq重复记录
old is : 旧
DELETE FROM pkrss_rssitem WHERE id in (select id from pkrss_rssitem where id>240000 group by item_title having count(item_title) > 1)
problem: 出现以下错误
#1093 - You can't specify target table 'pkrss_rssitem' for update in FROM cl
new: 修改后
DELETE FROM pkrss_rssitem WHERE id in (select B.id from (select id from pkrss_rssitem where id>240000 group by item_title having count(item_title) > 1) B )
because: 因为
In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:
DELETE FROM t WHERE ... (SELECT ... FROM t ...);UPDATE t ... WHERE col = (SELECT ... FROM t ...);{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the
FROM clause. Example:
UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
Here the prohibition does not apply because a subquery in the FROM clause is materialized as a temporary table, so the relevant rows in have already been selected by the time the update to takes place.
Comments