如何在MySQL中使用join for DELETE子句？

jvlzgdj9 于 2023-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(111)

我有以下查询：

delete from customers_cards
where id not in (
    select min(id)
     from customers_cards
     group by number_card )
and belongs_to = "ezpay"

它抛出：

1093 -无法在FROM子句中指定目标表'customers_cards'进行更新

我想我需要使用join作为解决方法，但老实说，我不能用join重写相同的逻辑。如何使用join编写上面的查询？

mysql

来源：https://stackoverflow.com/questions/76492413/how-to-use-join-for-delete-clause-in-mysql

2条答案

按热度按时间

disho6za1#

该连接应与select rows in one table but not in another的连接类似

DELETE c1 
FROM customers_cards AS c1
LEFT JOIN (
    SELECT MIN(id) AS id
    FROM customer_cards
    GROUP BY number_card
) AS c2 ON c1.id = c2.id
WHERE c2.id IS NULL AND c1.belongs_to = 'ezpay'

赞(0）回复(0）举报 2023-06-21

h6my8fg22#

这里有一个替代方法：
删除任何属于'ezpay'的行c1，前提是存在另一行c2，其中number_card与id相同，id较小。

DELETE c1
FROM customer_cards AS c1
LEFT OUTER JOIN customers_cards AS c2
  ON c1.number_card = c2.number_card AND c1.id > c2.id
WHERE c2.id IS NOT NULL AND c1.belongs_to = 'ezpay';

赞(0）回复(0）举报 2023-06-21

我来回答

如何在MySQL中使用join for DELETE子句？

1093 -无法在FROM子句中指定目标表'customers_cards'进行更新

2条答案

相关问题

热门标签

最新问答