SQL更新WHERE xx和最近的记录

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”的记录,我想只更新最新的记录(我有一个日期和一个id,可用于定义哪个记录是最新的)

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”之类的内容?

2 个解决方案

#1

You can limit to update only the most recent record

您可以限制仅更新最新记录

UPDATE your_table
SET some_column = 1
order by date_time_column desc
limit 1

where date_time_column can be any column indicating the order of the records. It could be an auto-increment ID too.

其中date_time_column可以是指示记录顺序的任何列。它也可以是自动增量ID。

#2

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子句,因此将是可移植的。请注意,inner_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.

如果内部条件获取比外部条件更广泛的结果集,则最终可能会得到一个date_time_column,该date_time_column早于满足外部条件的行中包含的date_time_column。然后对它们进行AND运算将获得0行。
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.

如果内部条件获取比外部条件更窄的结果集,则最终可能会错过任何更新的记录,而不是内部集合的一部分,但是作为外部条件的一部分已经满足。

Hope this clarifies.

希望这澄清一下。

#1