MySQL DELETE With a Sub-Query using Having and Cou

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
    )

Answer 2

Your problem is solved,just do as following..

    DELETE FROM account 

      WHERE guid IN 

     (SELECT * FROM 

       (SELECT guid FROM account 

          GROUP BY guid,type 

          HAVING  count(type) > 1) AS a);

Answer 3

First create view

create view view_acct as 
SELECT guid FROM account 
GROUP BY guid,type HAVING count(type) > 1;

After use view

DELETE FROM account WHERE guid in (select * from view_acct);