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
)