MySQL SQL 更新 WHERE xx AND 最近的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14505859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL update WHERE xx AND most recent record
提问by jrm
I need to do something that seems to be very simple:
我需要做一些看起来很简单的事情：
$bdd->query('UPDATE mytable SET aaa = \''.$aaa.'\', bbb = \''.$bbb.'\' WHERE name = \''.$name.'\'');
My problem: I have multiple records that match this "WHERE", and I want to update only the most recent one (I have a date and an id that can be used to define which record is the most recent)
我的问题：我有多个与此“WHERE”匹配的记录，我只想更新最新的记录（我有一个日期和一个可用于定义哪个记录是最新的记录）
How can I change my WHERE to add something like "AND id = the_highest_id_of_this_query"?
如何更改我的 WHERE 以添加类似“ AND id = the_highest_id_of_this_query”的内容？
回答by juergen d
You can limitto updateonly the most recent record
你只能limit到update最近的记录
UPDATE your_table
SET some_column = 1
order by date_time_column desc
limit 1
where date_time_columncan be any column indicating the order of the records. It could be an auto-increment ID too.
wheredate_time_column可以是指示记录顺序的任何列。它也可以是一个自动递增的 ID。
回答by alok
UPDATE table
SET column = value
WHERE primary_key =
(
SELECT primary_key
FROM table
WHERE date_time_column = (select max(date_time_column) FROM table WHERE other_conditions)
)
AND other_conditions
This query does not use order by or limit clause and therefore will be portable. Note that other_conditions have to be same in the inner query and outer query.
此查询不使用 order by 或 limit 子句，因此是可移植的。请注意，内部查询和外部查询中的 other_conditions 必须相同。
(Since this was posted in a comment) Why does the inner query need to have the same condition as the outer one?
（因为这是在评论中发布的）为什么内部查询需要与外部查询具有相同的条件？
If the inner condition fetches a broader resultset than the outer one, you could end up with a date_time_column that is earlier than those contained in the rows satisfying the outer condition.
ANDing them will then result in a fetch of 0 rows.If the inner condition fetches a narrower result set than the outer one, you could end up missing out on any of the records that are newer, not part of the inner set yet satisfied as part of the outer condition.
如果内部条件比外部条件获取更广泛的结果集，则最终可能会得到一个 date_time_column 早于满足外部条件的行中包含的结果集。
AND对它们进行操作将导致获取 0 行。如果内部条件获取比外部条件更窄的结果集，您最终可能会错过任何较新的记录，而不是作为外部条件的一部分满足的内部集合的一部分。
Hope this clarifies.
希望这能澄清。
