mysql - You can't specify target table 'inventory' for update in FROM clause

Question

Can you help me to solve this problem. I just want to update the price that does not include the other collection name

UPDATE inventory SET price = '450' WHERE id IN (SELECT id FROM inventory WHERE collection_name NOT IN ('C1','C2','C3','C4'))

Answer 1

MySQL doesn't allow this construct. Instead, use a JOIN. Assuming id is unique:

UPDATE inventory i JOIN
       (SELECT i2.id
        FROM inventory i2
        WHERE i2.collection_name NOT IN ('C1', 'C2', 'C3', 'C4')
       ) i2
       ON i.id = i2.id
    SET i.price = '450' ;