在MySQL中使用子查询删除

Question

我想转换以下内容

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

转换为等效的delete语句。但是，以下代码不起作用：

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

Answer 1

您可以使用多表DELETE语法在表之间执行外联接：

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