I recently needed to update a whole bunch of records using an IN() on the target table:
UPDATE table1 SET field1 = 'some value' WHERE table_id IN ( SELECT table_id FROM table1 WHERE field2 = 'another value' );
This isn’t possible due to the target table being used in the WHERE. But the workaround is simple enough, use a temporary table:
UPDATE table1 SET field1 = 'some value' WHERE table_id IN ( SELECT table_id FROM ( SELECT table_id FROM table1 WHERE field2 = 'another value' ) AS temporary_table );
The resulting temporary table will allow the usage of the IN() function.
It is a bit slower, but its a ‘workaround’
Failing that, you could get a list of the ID’s by doing a separate query and then pasting the results into the IN() but then you:
a) run the risk of data changing between the queries, and
b) having to do more work, i.e reformatting and replacing carriage returns with commas, etc.
It all depends on your database/table structure and each case has its own decisive factors.