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

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)

Answer 2

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

Answer 3

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

Answer 4

create table GIVEAWAYS_NEW as(select*from giveaways);

update giveaways set winner=1
where Id=(select max(Id)from GIVEAWAYS_NEW);

Answer 5

Make use of TEMP TABLE:

as follows:

UPDATE TABLE_NAME SET TABLE_NAME.IsActive=TRUE
WHERE TABLE_NAME.Id IN (
    SELECT Id
    FROM TEMPDATA
);

CREATE TEMPORARY TABLE TEMPDATA
SELECT MAX(TABLE_NAME.Id) as Id
FROM TABLE_NAME
GROUP BY TABLE_NAME.IncidentId;

SELECT * FROM TEMPDATA;

DROP TABLE TEMPDATA;

Answer 6

You can create a view of the subquery first and update/delete selecting from the view instead.. Just remember to drop the view after.