MySQL: delete from IN

mysql sql mysql-error-1093
jmccartie picture jmccartie · Feb 13, 2011 · Viewed 10.3k times · Source

I know this is a simple syntax issue. Trying to delete all users from a subquery:

delete from users
where id IN (

select u.id
from users u 
where not exists (select * from stickies i where i.user_id = u.id) 
group by u.email 
having count(*) > 1

)

Getting this error:

error : You can't specify target table 'users' for update in FROM clause

The subquery works fine (returns list of user id's).

Answer

The Scrum Meister picture The Scrum Meister · Feb 13, 2011 
DELETE u.*
FROM users u JOIN (
    SELECT u.id
    FROM users u LEFT JOIN stickies i ON i.user_id = u.id
    WHERE i.user_id IS NULL
    GROUP BY u.email 
    HAVING COUNT(*) > 1
  ) r ON r.id = r.id

Note: in the inner query, you are grouping by email, but selecting a user ID. this may return non deterministic results.

Related questions

You can't specify target table for update in FROM clause

I have a simple mysql table: CREATE TABLE IF NOT EXISTS `pers` ( `persID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(35) NOT NULL, `gehalt` int(11) NOT NULL, `chefID` int(11) DEFAULT NULL, PRIMARY KEY (`persID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; …

Read More
SQL DELETE with JOIN another table for WHERE condition

I have to delete rows from guide_category that have no relation with guide table (dead relations). Here is what I want to do, but it of course does not work. DELETE FROM guide_category AS pgc WHERE pgc.id_…

Read More
Insert and set value with max()+1 problems

I am trying to insert a new row and set the customer_id with max()+1. The reason for this is the table already has a auto_increatment on another column named id and the table will have multiple rows with …

Read More