MySQL #1093 - You can't specify target table 'giveaways' for update in FROM clause

Question

I tried:

UPDATE giveaways SET winner = '1' WHERE ID = (SELECT MAX(ID) FROM giveaways)

But it gives:

#1093 - You can't specify target table 'giveaways' for update in FROM clause

This article seems relevant but I can't adapt it to my query. How can I get it to work?

Solution 1

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.

I would think you could do it like this (untested):

UPDATE
    giveaways
SET
    winner = '1'
ORDER BY
    id DESC
LIMIT 1

Read more

Solution 2

Based on the information in the article you linked to this should work:

update giveaways set winner='1'
where Id = (select Id from (select max(Id) as id from giveaways) as t)

Solution 3

update giveaways set winner=1 
where Id = (select*from (select max(Id)from giveaways)as t)

Comments

[Gigi]

You're doing a recursive query. What do you want to do exactly?

[Eray]

There are fews record at giveaways table. I want to set giveaway's (which has biggest ID) winner column to 1

[Pacerier]

@Gigi, It's quite obvious what he wants to do. UPDATE giveaways SET winner = '1' WHERE ID = (SELECT MAX(ID) FROM giveaways)

[Eray]

Thank you it's working ! BUt can you expain, which way should i choose for performance ? YOurs ? Or @ipr101's (stackoverflow.com/a/8333445/556169) ?

[Eray]

Thank you it's working ! BUt can you expain, which way should i choose for performance ? YOurs ? Or @nick rulez's (stackoverflow.com/a/8333417/556169) ?

[Matthew]

@Eray both this answer and nick rulez' answer above are equivalent, but they both use nested subqueries which are not required here.

[Nicola Cossu]

My query and that of ipr101 are identical. They simply use a temporary table workaround to avoid the error you reported. By the way Matthew's solution is good too. ;)

[Eray]

@MatthewPK , Nick's answer selecting everything (SELECT *) , ipr101's just ID (SELECT id) . Because of this i think ipr101's solution has more performance. You know i mentioned a article, and in this article, writer using subqueries. I'll test your answer too.

[Eray]

It's tested and approved . It taking 0.0002 seconds .

[Nicola Cossu]

@Eray. I don't think my query could be slower than ipr101's because even though I use *, the query retrieves just one scalar value. So I don't get any unnecessary value. By the way I've upvoted Matthew's answer because is the smarter solution and I don't understand why someone has downvoted it.

[Pacerier]

@Matthew, Regarding "what if updating that record causes something to happen which made the WHERE condition FALSE", Since the where clause has already been evaluated, why should that matter? Why is MySQL not smart enough to do that (ipr101's and nick's answer) automatically?

[Pacerier]

Wouldn't the performance be horrible since you are duplicating the whole table?

[Pacerier]

@ipr101, Why not save select max(Id) as id from giveaways as a variable and refer to it via that variable?

[Matthew]

@Pacerier The reason is because those solutions use an implicit query against a nested subquery. when you tell engine to select content from a nested subquery you are implicitly forcing it to execute that statement first. Without doing that (as written) the query planner will try and optimize the nesting away. This is coding by coincidence which means you are depending on specific behavior of the query planner and is poor practice.

[Pacerier]

@Matthew, Then, isn't the real problem to do with "the query planner will try and optimize the nesting away"? In a query like update t where id=(select max(id)from t), it's clear that the query planner shouldn't try to optimize the nesting away because it can be seen that (select max(id)from t) is the inner query that should be run first.

[Matthew]

@Pacerier No. The query planner's default behavior is usually not to execute nested subqueries first because they may be able to be combined in out queries more effectively. The two answers making use of this explicity SELECT from it, forcing the query planner's behavior. This works, of course, but is working against the planner. It's coding by coincidence. You are not in control of how the query planner behave and you should not code to take advantage of its coincidental behavior.

[Matthew]

@Pacerier saving it to a variable and then using that variable would no longer be coding by coincidence, but would be introducing a race condition.

[Pacerier]

@Matthew, And that's the real problem. The query planner is here to optimize scripts where possible. And since it's smart enough to know that update t where id=(select max(id)from t) has a cycle, then it's pretty straightforward for it to tell itself to execute the nested subqueries first in these cases. For the cases where no cycles are detected, it is free to combine the queries in anyway it sees fit as long as the results are referentially transparent.

[Pacerier]

@Matthew, Of course we would be surrounding the statements with start transaction and commit. Then there would be no race conditions.

[Matthew]

@Pacerier transactions do not inherently create table locks.

[Pacerier]

@Matthew, set @a=(select max(id)from giveaways); update giveaways set winner='1' where id=@a Explain, How can we get a deadlock from that?

[Matthew]

@Pacerier You wouldn't, you'd get a race condition. If you didn't want a race condition you'd need a table lock. This isn't inherently a dead-lock.

[Doin]

Also it has concurrency issues: Since you're using multiple statements, you may need to wrap this in a transaction, otherwise the max Id in giveaways might change between the create table and the update.