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

Look here: http://stackoverflow.com/questions/45494/sql-delete-cant-specify-target-table-for-update-in-from-clause — galymzhan, Feb 08 '11 at 18:33

Yogendra Singh · Accepted Answer · 2012-11-28T17:04:40.123

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
    )

score 0 · Answer 2 · answered Dec 02 '13 at 11:20

0

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);

answered Dec 02 '13 at 11:20

Abhik Dey

391
2
12

Thank you Abhik but you can't delete from the same table you are selecting. – Luis Alvarado Jun 02 '16 at 22:26

score -1 · Answer 3 · edited Feb 26 '17 at 22:47

-1

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);

edited Feb 26 '17 at 22:47

denny

1,914
2
14
19

answered Feb 08 '11 at 18:54

Krunal

1,881
3
12
12

MySQL DELETE With a Sub-Query using Having and Count

3 Answers3

Linked