Problem with nested UPDATE queries 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!
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.