Geek Answers Handbook

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

I am trying to build a MySQL query that will UPDATE a row in my table. WHERE ID is the highest AND the field named idSession is 65. It looks like this:

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

And I get the error message:

"Error code: 1093. You cannot specify the target table" History "for updating in the FROM section."

Does anyone know what is wrong with my syntax?

+4
source share
3 answers

What he says: you cannot select from a table when you update the same table based on a condition from the same table. (This is intentionally vaguely written: p)

Try the following:

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

+16
source

How about this:

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

This is an imitation of MySQL. There are two ways around this (actually three, but I don't like the third).

1) Since the request:

 SELECT MAX(id) FROM History WHERE idSession = 65

gives the same results as:

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

you can use the solution provided by @xdazz and @Kolink by modifying the update to use the (native XML) ORDER BY syntax in the UPDATE .

2) The second way is to join your table with the above subquery. This works in more complex conditions / subqueries / joins that cannot be rewritten using the simple By order:

 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 ;
0
source

More articles:


All Articles