MySQL REMOVE Using Sub-Query Using Availability and Quantity

Question

I am trying to DELETE multiple entries using the following query:

First, I find the entries 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 cannot specify the "account" of the target table to update in the FROM clause

Answer 1

I think you need to use a temporary table to achieve your needs, as shown below:

• Step 1: create a temporary table

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

• Use the temp table in the delete statement

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

• Drop temporary table

   DROP TEMPORARY TABLE MyTemp; 

EDIT: I think working 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 resolved, just follow these steps.

  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 a view

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

After use

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