How to use join for DELETE clause 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 throws:

#1093 - You can't specify target table 'customers_cards' for update in FROM clause

I guess I need to use join as a workaround, but honestly, I cannot rewrite the same logic with join. Any idea how can I write the query above with join?

Barmar · Accepted Answer · 2023-06-16T17:42:37.240

1

The join should be similar to what you would use to 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'

edited Jun 16 '23 at 17:42

answered Jun 16 '23 at 17:31

Barmar

741,623
53
500
612

Thanks, upvoted, just should I put `belongs_to = "ezpay"` inside the parentheses or outside? – Martin AJ Jun 16 '23 at 17:41
1

Sorry, missed that part. I've added it to the answer. – Barmar Jun 16 '23 at 17:42

score 1 · Answer 2 · answered Jun 16 '23 at 18:55

Here's an alternative method:

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

How to use join for DELETE clause in MySQL?

2 Answers2