使用JOIN在MySQL中執行DELETE操作的方法

Question

我有以下查詢：

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來寫上面的查詢？

Answer 1

這裡是另一種方法：

刪除屬於'ezpay' 的任何行c1，前提是存在另一行c2，它具有相同的number_card 和較小的 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';

Answer 2

這個連接應該類似於你在一個表中選擇行但不在另一個表中選擇的方法。

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'