0

How can I make this work - gives me #1093 - You can't specify target table 'Orders' for update in FROM clause

Update Orders set flag = 0 where orders_id = (select orders_id from orders ORDER BY orders_id DESC LIMIT 1)
Manish Pradhan
  • 1,141
  • 10
  • 28

2 Answers2

1

You need to wrap it in a subquery (creating a temporary table)

Update Orders 
set flag = 0 
where orders_id = 
    (
        SELECT x.orders_id
        FROM
        (
            select orders_id 
            from orders 
            ORDER BY orders_id DESC 
            LIMIT 1
        ) x
    )

or 

Update Orders 
set flag = 0 
where orders_id = 
    (
        SELECT x.orders_id
        FROM
        (
            select MAX(orders_id) orders_id 
            from orders 
        ) x
    )
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • It should be MAX in your seconds query as OP is ordering it by desc – AnandPhadke Oct 16 '12 at 14:14
  • @AnandPhadke because you need to create a temporary table for it. I'll show an example for you. Please wait :) – John Woo Oct 16 '12 at 14:17
  • @AnandPhadke [See the demo. try to uncomment the commented query (which is the same as your query) and it will not build.](http://sqlfiddle.com/#!2/5b884/1) – John Woo Oct 16 '12 at 14:22
  • @JohnWoo Can you tell me how I can get the last_insert_id to work with this update statement, I know it has something to do with ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) but its not working for me! – Manish Pradhan Oct 16 '12 at 14:23
  • @ManishPradhan try `ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID()` – John Woo Oct 16 '12 at 14:31
  • @JohnWoo , i tried but it does not work, may be I am putting it in the wrong place. I put it at the end..? – Manish Pradhan Oct 16 '12 at 14:46
  • actually `ON DUPLICATE KEY UPDATE` works on `INSERT` statements. – John Woo Oct 16 '12 at 14:51
  • @ManishPradhan [INSERT ... ON DUPLICATE KEY UPDATE Syntax](http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html) – John Woo Oct 16 '12 at 14:53
0

it looks like you are trying up update the last order in the table? try

Update Orders set flag = 0 ORDER BY orders_id DESC LIMIT 1;

Mysql doesn't support updating a table while using it in a subquery..

Doon
  • 19,719
  • 3
  • 40
  • 44
  • Why is there a minus vote on this answer, can anyone explain because it works just fine..? – Manish Pradhan Oct 16 '12 at 14:16
  • yeah I was curious as to the downvote? it avoids creating the temp table, and the sub select, and just does it all in 1 update. – Doon Oct 16 '12 at 14:18
  • Can you tell me how I can get the last_insert_id to work with this update statemtent, I know it has something to do with ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) but its not working for me! – Manish Pradhan Oct 16 '12 at 14:22
  • not sure exactly what you are asking? Last_insert_id doesn't mean anything on updates? especially if part of an on duplicate key – Doon Oct 16 '12 at 14:34
  • OK let me explain, like you said, I am trying to update the last order in the table. Your query does that nicely but I also need the ID of the last row that was updated. Is it possible with your sql query by adding something to it? – Manish Pradhan Oct 16 '12 at 14:41
  • never tried, but the technique listed here should help. http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql – Doon Oct 16 '12 at 14:51
  • This worked - Update Orders set flag = 0, orders_id = LAST_INSERT_ID(orders_id) ORDER BY orders_id DESC LIMIT 1; – Manish Pradhan Oct 16 '12 at 15:01