mysql having count,MySQL使用Haveing和Count进行子查询删除

Am trying to DELETE several entries using the following Query:

First i find the entries that i want to delete using this query:

SELECT guid FROM account GROUP BY guid,type HAVING count(type) > 1);

Then i add this query to the DELETE statement:

DELETE FROM account WHERE guid IN (SELECT guid FROM account GROUP BY guid,type HAVING count(type) > 1);

But i get this error:

You can't specify target table 'account' for update in FROM clause

解决方案

I think you need to use temporary table to achieve your need as below:

Step1: Create temp table

CREATE TEMPORARY TABLE MyTemp

SELECT guid FROM account

GROUP BY guid,type HAVING count(type) > 1;

Use the temp table in your delete statement

DELETE FROM account

WHERE guid IN (SELECT guid FROM MyTemp);

Drop the temp table

DROP TEMPORARY TABLE MyTemp;

EDIT: I think a work around with *two nested tables also works:

DELETE FROM account

WHERE guid IN

(SELECT guid FROM

(SELECT guid FROM account

GROUP BY guid,type HAVING count(type) > 1) as MyTemp

)