ID最高且字段等于变量的MySQL UPDATE查询 [英] MySQL UPDATE query where id is highest AND field is equal to variable

问题描述

我正在尝试构造一个MySQL查询，该查询将更新ID为最高且表中idSession等于65的表中的行.看起来像这样:

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

我收到错误消息:

"错误代码:1093.您无法指定目标表历史记录"进行更新 在FROM子句中."

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

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

Anyone know what's wrong with my syntax?

推荐答案

它的确切含义:当您基于完全相同的表中的条件更新同一表时，无法从该表中进行选择. (故意把它写成:p)

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)

尝试一下:

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

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

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

这篇关于ID最高且字段等于变量的MySQL UPDATE查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！