英文:
How to delete users who have not added card?
问题
Here's the translated code snippet:
我有以下查询：
选择 u.*
从用户 u
左连接 customers_cards cc
在 cc.user_id = u.id 上
其中 u.belongs_to = "ezpay"
并且 cc.id 是空的
它返回尚未添加任何卡的用户。我需要从“users”表中删除这些用户。因此，这是我的“删除”查询：
从用户中删除
其中 id 在（选择 u.id
从用户 u
左连接 customers_cards cc
在 cc.user_id = u.id 上
其中 u.belongs_to = "ezpay"
并且 cc.id 是空的）
但它抛出以下错误：
> #1093 - 你不能在FROM子句中指定目标表'users'进行更新
如何修复它？
英文:
I have the following query:
select u.*
from users u
left join customers_cards cc
on cc.user_id = u.id
where u.belongs_to = "ezpay"
and cc.id is null
It returns users who have not added any card yet. I need to delete these users (from users) table. So here is my delete query:
delete from users
where id in ( select u.id
from users u
left join customers_cards cc
on cc.user_id = u.id
where u.belongs_to = "ezpay"
and cc.id is null )
But it throws the following error:
> #1093 - You can't specify target table 'users' for update in FROM clause
How can I fix it?
答案1
得分: 1
没有必要使用IN运算符，因为您的SELECT语句可以转换为DELETE语句：
delete u.*
from users u
left join customers_cards cc
on cc.user_id = u.id
where u.belongs_to = "ezpay"
and cc.id is null;
英文:
There is no need to use the IN operator because your SELECT statement can be transformed into a DELETE statement:
delete u.*
from users u
left join customers_cards cc
on cc.user_id = u.id
where u.belongs_to = "ezpay"
and cc.id is null;
答案2
得分: 1
你可以选择使用否定的 EXISTS 操作符。
DELETE FROM users
WHERE NOT EXISTS(SELECT 1
FROM customer_cards
WHERE users.id = customer_cards.id)
AND belongs_to = "ezpay"
英文:
You can optionally use the negated EXISTS operator.
DELETE FROM users
WHERE NOT EXISTS(SELECT 1
FROM customer_cards
WHERE users.id = customer_cards.id)
AND belongs_to = "ezpay"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论