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