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?

Answer 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