Update multiple rows using WHERE IN where using one MYSQL query

Question

I have a one query for select ids from table and another query for update the table values. These are the queries used.

for select ids

select id from table_1 where orderId = 41 AND status = 1

for update

UPDATE table_1 SET orderId = '17' WHERE id IN (1,2,3,4,5,6,.....,n);

This two queries working properly. But when try to this code its not working. I want to update orderId update 41 to 17 in all the records selected

UPDATE table_1 SET orderId = '17' WHERE id IN (select id from table_1 where orderId = 41 AND status = 1 );

This query is returns error. Any suggestion to correct this error. Thank You

Answer 1

The problem is that MySQL does not allow you to use the same table in subqueries in an update or delete. I think you can simplify the logic, so the subquery is not needed.

Why not just use this?

UPDATE table_1
    SET order_id = '17'
    WHERE order_id = 41 AND status = 1;

Note: If order_id is a number, use 17 not '17' -- don't mix data types.

This assumes that id is unique.

Alternatively, if you really need list of ids, you can also use a JOIN:

UPDATE table_1 t1 JOIN
       (SELECT tt1.*
        FROM table_1 tt1
        WHERE tt1.order_id = 41 AND tt1.status = 1
       ) tt1
       ON tt1.id = t1.id
    SET t1.order_id = 17;