how to solve Error Code: 1093. You can't specify target table 'dcp' for update in FROM clause mysql

Question

I have a query like this :

DELETE dcp 
FROM discount_campaign_product dcp 
JOIN product_categories pc ON dcp.product_id = pc.product_id 
JOIN categories c ON pc.category_id = c.id 
WHERE dcp.discount_campaign_id = discount_campaign_id AND
    FIND_IN_SET(c.id, delete_category) AND 
    EXISTS(SELECT 1 
           FROM discount_campaign_product dcp2 
           WHERE dcp.product_id = dcp2.product_id AND 
                 dcp2.discount_campaign_id = discount_campaign_id);

but I get this error

Error Code: 1093. You can't specify target table 'dcp' for update in FROM clause

I tried with this solution https://stackoverflow.com/a/45498/12977554

with this query SET optimizer_switch = 'derived_merge=off';

but still produce same error

You are trying to delete from a table and use the same table in `WHERE` clause at the same time. What's the purpose of the `EXISTS` operator? — Giorgos Betsos, Feb 16 '21 at 11:06
Basically i want to delete some records on dcp table where the records is exists on dcp table — 18818181881, Feb 16 '21 at 11:13
So if there is duplicate delete all of them, or leave just one? — Giorgos Betsos, Feb 16 '21 at 11:14
all of them, because i want to delete all the records based on the condition — 18818181881, Feb 16 '21 at 11:16

score 1 · Accepted Answer · answered Feb 16 '21 at 11:20

Try this:

DELETE dcp 
FROM discount_campaign_product dcp 
JOIN product_categories pc ON dcp.product_id = pc.product_id 
JOIN categories c ON pc.category_id = c.id 
WHERE dcp.discount_campaign_id = discount_campaign_id AND
    FIND_IN_SET(c.id, delete_category) AND
    product_id IN (SELECT product_id
                   FROM (
                      SELECT product_id
                      FROM discount_campaign_product
                      GROUP BY product_id
                      HAVING COUNT(*) > 1) t)

how to solve Error Code: 1093. You can't specify target table 'dcp' for update in FROM clause mysql

1 Answers1