使用JOIN删除选择的结果 [英] delete results of a select with a JOIN

问题描述

我有一个users表格，该表格可能已上传或未上传photos.我想删除所有没有有照片的用户.现在，我可以使用户没有像这样的照片:

I have a table of users that may or may not have photos uploaded. I want to delete any users that don't have photos. Right now I can get the users with no photos like this:

SELECT a.id FROM users a
LEFT JOIN images b
ON a.id = b.user_id
WHERE b.user_id is null

但是，我对如何真正进行删除有些困惑.

However, I'm a little stumped on how to actually make the delete happen.

这不起作用:

DELETE FROM users c WHERE c.id IN ( SELECT a.id FROM users a
    LEFT JOIN images b
    ON a.id = b.user_id
    WHERE b.user_id is null
    )

这也不是:

DELETE FROM users a
LEFT JOIN images b
ON a.id = b.user_id
WHERE b.user_id is null

删除JOIN结果的正确方法是什么?

What's the right way to do a delete on the results of a JOIN?

推荐答案

我猜你的第二个示例失败了，因为它引用了子查询中的表，但实际上并不需要:

I am guessing your 2nd example failing because it is referencing the table in the subquery, but it shouldn't actually need to:

DELETE FROM users c WHERE c.id NOT IN ( SELECT DISTINCT user_id FROM images);

否则，我会这样做:

DELETE FROM users 
USING users AS u LEFT JOIN images AS i ON u.id = i.user_id 
WHERE i.user_id IS NULL;

这篇关于使用JOIN删除选择的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！