By Eray


2011-11-30 21:46:57 8 Comments

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?

6 comments

@Rafael 2016-07-11 02:49:04

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

@DARSHAN SHINDE 2016-01-11 10:48:52

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;

@Matthew 2011-11-30 21:53:02

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

@Eray 2011-11-30 22:05:05

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

@Pacerier 2015-02-24 03:07:39

@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?

@Matthew 2015-03-02 21:28:34

@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 2015-03-09 00:03:31

@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 2015-03-09 22:51:18

@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.

@Pacerier 2015-03-11 10:17:35

@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.

@Tarun 2014-11-28 09:27:51

create table GIVEAWAYS_NEW as(select*from giveaways);

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

@Pacerier 2015-02-24 03:13:22

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

@Doin 2016-04-17 09:08:07

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.

@Nicola Cossu 2011-11-30 21:50:22

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

@Eray 2011-11-30 21:56:11

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) ?

@Nicola Cossu 2011-11-30 21:59:11

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. ;)

@ipr101 2011-11-30 21:52:20

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)

@Eray 2011-11-30 21:56:28

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 2011-11-30 21:58:24

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

@Eray 2011-11-30 22:01:29

@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.

@Nicola Cossu 2011-11-30 22:09:45

@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.

@Sayka 2014-12-05 17:15:33

awesome.........

@Pacerier 2015-02-24 03:30:07

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

@Matthew 2015-03-09 22:52:10

@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 2015-03-11 10:21:19

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

@Matthew 2015-03-11 22:23:46

@Pacerier transactions do not inherently create table locks.

@Pacerier 2015-03-13 13:50:39

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

@Matthew 2015-03-13 16:25:30

@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.

Related Questions

Sponsored Content

15 Answered Questions

[SOLVED] MySQL Error 1093 - Can't specify target table for update in FROM clause

10 Answered Questions

[SOLVED] You can't specify target table for update in FROM clause

1 Answered Questions

[SOLVED] #1093 - You can't specify target table 'tbl' for update in FROM clause

  • 2017-12-22 08:05:50
  • VVB
  • 405 View
  • 1 Score
  • 1 Answer
  • Tags:   mysql

2 Answered Questions

[SOLVED] Delete - I can't specify target table?

2 Answered Questions

2 Answered Questions

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

  • 2016-03-09 08:52:13
  • usman
  • 63 View
  • 2 Score
  • 2 Answer
  • Tags:   php mysql

1 Answered Questions

[SOLVED] #1093 - You can't specify target table 'comments' for update in FROM clause

  • 2016-02-09 11:39:55
  • Khalil Enn
  • 155 View
  • 1 Score
  • 1 Answer
  • Tags:   mysql duplicates

2 Answered Questions

1 Answered Questions

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

  • 2014-06-10 12:29:06
  • Matheus Hernandes
  • 513 View
  • 2 Score
  • 1 Answer
  • Tags:   mysql sql

Sponsored Content