Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
Please add: "assuming that id is the primary key of Tab1" If not, a slightly more complex join will be needed. –  ypercube Jan 23 '13 at 14:30
    
@ypercube: I updated the question. Thank you. –  Pietro Jan 23 '13 at 14:35
add comment

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

share|improve this answer
    
I wonder if MySQL ever gets their engine correct in order to allow such things. –  a_horse_with_no_name Jan 23 '13 at 16:50
    
@a_horse_with_no_name only Larry Ellison can answer that :( –  RolandoMySQLDBA Jan 23 '13 at 17:03
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.