Problem with nested UPDATE queries

Question

I cannot make an update query work. As a first step, the following one works correctly:

UPDATE Tab1  
SET Tab1.a = '3'
WHERE
    Tab1.id IN ( 123, 456 );

where id is the primary key of Tab1.
However when I add a select to get the set of values Tab1.id must be in, I get the following MySQL error:

Error code: 1093
You can't specify target table 'Tab1' for update in FROM clause.

This is the complete query:

UPDATE Tab1  
    SET Tab1.a = '3'
    WHERE
        Tab1.id IN ( 
            SELECT Tab1.id 
            FROM Tab1, Tab2 
            WHERE Tab1.b = Tab2.b AND Tab1.c = '4'
        );

I cannot see how to fix this error.
Am I forced to split this query in two?

Thank you!

Answer 1

Try

UPDATE Tab1 JOIN Tab2 ON Tab1.b = Tab2.b
SET Tab1.a = '3'
WHERE Tab1.c = '4'
;

Check the documentation for further information (look for 'multiple-table syntax'). An important point is:

If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly. See Section 14.3.5.4, “FOREIGN KEY Constraints”.

You may check a similar example on SQLFiddle.

Answer 2

SUPPLEMENTARY INFORMATION

Specifying UPDATEs using subqueries can be dangerous business.

Back on Feb 21, 2011 someone asked this question : Problem with MySQL subquery.

I did a little research and discovered something disturbing: During the optimization of a query that involves a subquery against itself, it is entirely possible for rows to intermittently disappear.

Therefore, please consider the error message

Error code: 1093
You can't specify target table 'Tab1' for update in FROM clause.

a friendly warning shot from MySQL not to write queries with that style

UPDATE Tab1  
    SET Tab1.a = '3'
    WHERE
        Tab1.id IN ( 
            SELECT Tab1.id 
            FROM Tab1, Tab2 
            WHERE Tab1.b = Tab2.b AND Tab1.c = '4'
        );

@dezso's answer (gets a +1), which employs the use of an UPDATE JOIN, is more palatable to the MySQL Query Optimizer

Answer 3

In this case, @dezso's answer is the right choice; however, there is another approach that sometimes comes in handy when faced with an update that references the same table.

When you need to update a table based on something you want to derive from the table itself, you can quite consistently persuade the optimizer to materialize an uncorrelated subquery into an implicit temporary table prior to processing the outer query.

UPDATE Tab1  
    SET Tab1.a = '3'
    WHERE
        Tab1.id IN ( 
            SELECT * FROM (                            /* add this line */ 
                SELECT Tab1.id 
                FROM Tab1, Tab2 
                WHERE Tab1.b = Tab2.b AND Tab1.c = '4'
            ) hack                                     /* and this line */
        );

Where hack is an alias for that temporary table. Without some kind of name there (and an optional AS before the name):

ERROR 1248 (42000): Every derived table must have its own alias

This is actually documented behavior, and comes in rather handy when there's not a more appropriate solution, like the join.

Here the result from the subquery in the FROM clause is stored as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place.

http://dev.mysql.com/doc/refman/5.5/en/subquery-restrictions.html