You can't specify target table (UPDATE on SELECT Query )?

Question

Query :

UPDATE `master_customer`  
SET `customer_group_id` = 19 
WHERE `customer_code` IN( SELECT * 
                          FROM `master_customer` a,
                               `master_customer_group` b
                          WHERE a.`customer_group_id` = b.`id`
                            AND b.`id` = 8);

Result:

Query: UPDATE `master_customer` SET `customer_group_id` = 19 WHERE `customer_code` IN( SELECT a.`customer_code` FROM `master_customer` ...

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

You can see here https://www.dofactory.com/sql/subquery – Mar 04 '20 at 12:02 — , Mar 04 '20 at 12:02

score 0 · Answer 1 · answered Mar 04 '20 at 12:06

It is a MySQL feature. You can work it around by wrapping subquery to yet another subquery :)

UPDATE `master_customer`  SET `customer_group_id` = 19 WHERE `customer_code` IN( 
SELECT x.customer_code FROM (
SELECT a.customer_code FROM
    `master_customer` a,
    `master_customer_group` b
    WHERE
    a.`customer_group_id` = b.`id`
    AND b.`id` = 8) AS x
)

https://stackoverflow.com/a/33607415/272885

You can't specify target table (UPDATE on SELECT Query )?

1 Answers1