Question 187927 in Mysql Question posted in
The official MySQL documentation can be found here.

How to use join for DELETE clause in MySQL?

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?

Tags:

2

Answers


  1. 0

    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'
    Login or Signup to reply.
  2. 0

    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';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search