SQL update WHERE xx AND most recent record

Question

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)

How can I change my WHERE to add something like "AND id = the_highest_id_of_this_query"?

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

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

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

Hope this clarifies.