DELETE using a subquery in MySQL

Question

I'd like to convert the following

SELECT * `members` m
WHERE NOT EXISTS
  (SELECT s.mid
   FROM   memberships s 
   WHERE  s.mid = m.id);

to the equivalent delete statement. However, the code below doesn't work:

DELETE FROM `members` m
WHERE NOT EXISTS
  (SELECT s.mid
   FROM   memberships s 
   WHERE  s.mid = m.id);

eggyal · Accepted Answer · 2012-06-06 16:36:17Z

2

You can use the multiple-table DELETE syntax to perform an outer join between the tables:

DELETE FROM members
USING members LEFT JOIN memberships ON memberships.mid = members.id
WHERE memberships.mid IS NULL

answered Jun 6, 2012 at 16:36

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1