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.