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?

You're doing a recursive query. What do you want to do exactly? — Gigi, Nov 30 '11 at 21:48
There are fews record at *giveaways* table. I want to set giveaway's (which has biggest ID) winner column to 1 — Eray, Nov 30 '11 at 21:50
@Gigi, It's quite obvious what he wants to do. *`UPDATE giveaways SET winner = '1' WHERE ID = (SELECT MAX(ID) FROM giveaways)`* — Pacerier, Feb 24 '15 at 03:05

score 38 · Answer 1 · edited Feb 24 '15 at 03:09

38

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)

edited Feb 24 '15 at 03:09

Pacerier

76,400
86
326
602

answered Nov 30 '11 at 21:52

ipr101

23,344
6
55
60

1

Thank you it's working ! BUt can you expain, which way should i choose for performance ? YOurs ? Or @nick rulez's (http://stackoverflow.com/a/8333417/556169) ? – Eray Nov 30 '11 at 21:56
@Eray both this answer and nick rulez' answer above are equivalent, but they both use nested subqueries which are not required here. – Matthew Nov 30 '11 at 21:58
@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 Nov 30 '11 at 22:01
2

@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. – Nicola Cossu Nov 30 '11 at 22:09
@ipr101, Why not save `select max(Id) as id from giveaways` as a variable and refer to it via that variable? – Pacerier Feb 24 '15 at 03:30
1

@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. – Matthew Mar 09 '15 at 22:52
@Matthew, Of course we would be surrounding the statements with `start transaction` and `commit`. Then there would be no race conditions. – Pacerier Mar 11 '15 at 10:21
@Pacerier transactions do not inherently create table locks. – Matthew Mar 11 '15 at 22:23
@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? – Pacerier Mar 13 '15 at 13:50
1

@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. – Matthew Mar 13 '15 at 16:25

score 29 · Accepted Answer · edited Apr 17 '15 at 04:15

29

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

edited Apr 17 '15 at 04:15

Techie

42,101
38
144
232

answered Nov 30 '11 at 21:53

Matthew

9,896
5
43
95

2

It's tested and approved . It taking 0.0002 seconds . – Eray Nov 30 '11 at 22:05
@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 Feb 24 '15 at 03:07
1

@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. – Matthew Mar 02 '15 at 21:28
1

@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. – Pacerier Mar 09 '15 at 00:03
@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 Mar 09 '15 at 22:51
@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 Mar 11 '15 at 10:17

score 13 · Answer 3 · edited Feb 24 '15 at 03:10

13

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

edited Feb 24 '15 at 03:10

Pacerier

76,400
86
326
602

answered Nov 30 '11 at 21:50

Nicola Cossu

49,868
15
89
95

Thank you it's working ! BUt can you expain, which way should i choose for performance ? YOurs ? Or @ipr101's (http://stackoverflow.com/a/8333445/556169) ? – Eray Nov 30 '11 at 21:56
1

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. ;) – Nicola Cossu Nov 30 '11 at 21:59

score 0 · Answer 4 · edited Feb 24 '15 at 03:11

0

create table GIVEAWAYS_NEW as(select*from giveaways);

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

edited Feb 24 '15 at 03:11

Pacerier

76,400
86
326
602

answered Nov 28 '14 at 09:27

Tarun

17
1

3

Wouldn't the performance be horrible since you are duplicating the whole table? – Pacerier Feb 24 '15 at 03:13
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`. – Doin Apr 17 '16 at 09:08

score 0 · Answer 5 · edited Jan 11 '16 at 10:53

0

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;

edited Jan 11 '16 at 10:53

Tunaki

116,530
39
281
370

answered Jan 11 '16 at 10:48

DARSHAN SHINDE

21
3

score 0 · Answer 6 · answered Jul 11 '16 at 02:49

0

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

answered Jul 11 '16 at 02:49

Rafael

21
2

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

6 Answers6

Linked