CODEWITHSUNDEEP
mysql
urig
urig

Reputation: 16841

Why can't MySQL update a table and select from the same table in a subquery

At the bottom of the MySQL v5.6 documentation page for the UPDATE command it says:

You cannot update a table and select from the same table in a subquery.

For example, this will fail:

UPDATE actor
SET last_name = 'foo'
WHERE actor_id IN (
    SELECT actor_id 
    FROM actor 
    WHERE last_name = 'bar');

Note: Please assume the column last_name is not a part of an index.

Why is this not allowed and is there a stable workaround for it?

P.S. - Thanks for the workaround! Can you please also explain why the example above is not allowed?

Upvotes: 1

Views: 887

Answers (2)

Rahul
Rahul

Reputation: 18567

This is a simple trick:

UPDATE actor
SET last_name = 'foo'
WHERE last_name = 'bar'

Whatever you are doing is same as the above query.

Explanation: You can't just update something you are selecting.

If you want to go with your flow still then give an alias to sub-query by using temporary table,

UPDATE actor a1
SET a1.last_name = 'foo'
WHERE a1.actor_id IN (
    SELECT a2.actor_id 
    FROM actor a2
    WHERE a2.last_name = 'bar');

OR

UPDATE actor a1 SET a1.last_name='foo' 
WHERE a1.actor_id IN (SELECT a2.actor_id 
                    from (SELECT * FROM actor) a2 
                    WHERE a2.last_name = 'bar');

In other words in MySQL, you can’t modify the same table which you use in the SELECT part. This behavior is documented here.

And on stackoverflow link1 and link2.

Note:This is because your update could be cyclical… what if updating that record causes something to happen which made the WHERE condition FALSE? You know that isn’t the case, but the engine doesn’t. There also could be opposing locks on the table in the operation.

Upvotes: 2

sun_jara
sun_jara

Reputation: 1815

Have you tried to avoid the subquery?

UPDATE actor
SET last_name = 'foo'
WHERE last_name = 'bar';

Upvotes: 1

Related Questions