CODEWITHSUNDEEP
mysqlsql
user1301563
user1301563

Reputation: 73

How to make a select statement in update?

I need to update a table, and the Where clause should contain the last (or max) from a certain column, so I made this query:

UPDATE Orders
SET Ordermethod='Pickup'
WHERE orderid IN (
  SELECT MAX(orderid)
  FROM Orders);

But, for some reason I don't understand, mysql returns this error:

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

I tried different queries, which aren't working either... Can someone help??

Sorry for the crappy english

Upvotes: 2

Views: 127

Answers (2)

user1285324
user1285324

Reputation:

UPDATE Orders SET Ordermethod='Pickup' WHERE orderid IN( SELECT MAX(orderid) FROM ( SELECT * FROM Orders ) AS c1 )

Upvotes: 0

ruakh
ruakh

Reputation: 183251

This is a MySQL limitation. (As the documentation puts it: "Currently, you cannot update a table and select from the same table in a subquery.") You can work around the limitation by writing your subquery as (SELECT * FROM (SELECT ...) t), so that MySQL will create a temporary table for you:

UPDATE Orders
   SET Ordermethod='Pickup'
 WHERE orderid IN
        ( SELECT *
            FROM ( SELECT MAX(orderid)
                     FROM Orders
                 ) t
        )
;

Upvotes: 3

Related Questions