英文:
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 uleft join customers_cards ccon cc.user_id = u.idwhere 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 userswhere id in ( select u.idfrom users uleft join customers_cards ccon cc.user_id = u.idwhere 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 uleft join customers_cards ccon cc.user_id = u.idwhere 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 uleft join customers_cards ccon cc.user_id = u.idwhere u.belongs_to = "ezpay"and cc.id is null;
答案2
得分: 1
你可以选择使用否定的 EXISTS 操作符。
DELETE FROM usersWHERE NOT EXISTS(SELECT 1FROM customer_cardsWHERE users.id = customer_cards.id)AND belongs_to = "ezpay"
英文:
You can optionally use the negated EXISTS operator.
DELETE FROM usersWHERE NOT EXISTS(SELECT 1FROM customer_cardsWHERE users.id = customer_cards.id)AND belongs_to = "ezpay"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论