MySQL UPDATE query where id is highest AND field is equal to variable

Question

I'm trying to construct a MySQL query that will UPDATE a row in my table WHERE the id is highest AND a field called idSession is equal to 65. It looks like this:

UPDATE `History` 
SET `state` = 0 
WHERE `id` = (SELECT MAX(id) FROM `History` WHERE `idSession` = 65);

And I'm getting the error message:

"Error Code: 1093. You can't specify target table 'History' for update in FROM clause".

Anyone know what's wrong with my syntax?

Answer 1

Exactly what it says: You can't select from a table when you're updating that same table based on a condition from the exact same table. (That's intentionally confusingly written :p)

Try this:

UPDATE `History` SET `state`=0 WHERE `idSession`=65 ORDER BY `id` DESC LIMIT 1

You can use ORDER and LIMIT in UPDATE and DELETE queries ;)

Answer 2

What about this:

UPDATE `History`
SET `state` = 0
WHERE `idSession` = 65
ORDER BY `id` DESC 
LIMIT 1

Answer 3

This is a MySQL imitation. There are two ways around this (actually three but I don't like the 3rd one).

1) Because the query:

SELECT MAX(id) 
FROM History 
WHERE idSession = 65

produces same results as the:

SELECT id 
FROM History 
WHERE idSession = 65
ORDER BY id DESC
  LIMIT 1

you can use the solution provided by @xdazz and @Kolink, changing the update to use the (proprietary MySQL) syntax of ORDER BY in the UPDATE statement.

2) The second way around this to join your table with the above subquery. This works in more complex conditions/subqueries/joins, that cannot be rewritten with a simple Order By:

UPDATE 
      History AS h
  JOIN
      ( SELECT MAX(id) AS max_id 
        FROM History 
        WHERE idSession = 65
      ) AS m
    ON m.max_id = h.id
SET h.state = 0 ;