CODEWITHSUNDEEP
mysqlsubquerysql-delete
kylex
kylex

Reputation: 14406

DELETE using a subquery in MySQL

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

Upvotes: 0

Views: 193

Answers (1)

eggyal
eggyal

Reputation: 126035

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

Upvotes: 2

Related Questions