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?