update in

You can’t specify target table ‘xxx’ for update in FROM clause

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.