How to use JOIN to perform DELETE operations in MySQL

Question

I have the following query:

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

It reports an error:

#1093 - In the FROM clause, the target table 'customers_cards' cannot be specified for the update

I guess I need to use join as a workaround, but honestly I can't rewrite the same logic using join. Is there a way to write the above query using join?

Answer 1

Here is another way:

Delete any row c1 that belongs to 'ezpay', provided there is another row c2 with the same number_card and a smaller 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

This join should be similar to how you would select rows in one table but not the other.

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'