MySQL：从 IN 中删除

发布于 2024-10-17 05:25:15 字数 376 浏览 4 评论 0原文

我知道这是一个简单的语法问题。尝试从子查询中删除所有用户：

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

)

收到此错误：

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

子查询工作正常（返回用户 ID 列表）。

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

鹊巢 2024-10-24 05:25:15

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

注意：在内部查询中，您按电子邮件分组，但选择用户 ID。这可能会返回不确定的结果。

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.

回复收藏 0 原文

~没有更多了~