MySQL UPDATE查询，其中id最高AND字段等于变量

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:

我正在尝试构建一个MySQL查询,它将在我的表中更新一行,其中id为最高,而一个名为idSession的字段等于65.它看起来像这样:

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".

“错误代码:1093。您无法在FROM子句中为更新指定目标表'历史'”。

Anyone know what's wrong with my syntax?

有人知道我的语法有什么问题吗?

3 个解决方案

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

正是它的含义:当您根据完全相同的表中的条件更新同一个表时,无法从表中进行选择。 (这是故意混淆写的: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 ;)

您可以在UPDATE和DELETE查询中使用ORDER和LIMIT;)

#2

What about this:

那这个呢:

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

#3

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

这是一个MySQL模仿。有两种方法(实际上是三种,但我不喜欢第三种)。

1) Because the query:

1)因为查询:

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.

您可以使用@xdazz和@Kolink提供的解决方案,更改更新以在UPDATE语句中使用ORDER BY的(专有MySQL)语法。

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:

2)第二种方法是将你的表与上面的子查询连接起来。这适用于更复杂的条件/子查询/连接,无法使用简单的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 ;

#1