MySQL DELETE With a Sub-Query using Having and Count

Question

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

Answer 1

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

1. Step1: Create temp table

   CREATE TEMPORARY TABLE MyTemp
   SELECT guid FROM account 
   GROUP BY guid,type HAVING count(type) > 1;
   

2. Use the temp table in your delete statement

   DELETE FROM account 
   WHERE guid IN (SELECT guid FROM MyTemp);
   

3. 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
    )