updating one row with current timestamp based on it's id

Question

I have a table like so:

id | date    | date2
3  |2019-1-6| NULL
2  |2019-1-5| NULL
1  |2019-1-2| NULL

I want my query to update date2 only for the newest id:

id | date    | date2
3  |2019-1-6 | 2019-1-7
2  |2019-1-5 | NULL
1  |2019-1-2 | NULL

I tried this but it won't let me use select clause in where while using an update query.

update table set data2=current_timestamp where id=(select max(id) from table)

juergen d · Answer 1 · 2019-01-07T16:42:04.383

2

You can define in which order the records are being updated and stop after the first one:

update your_table 
set data2 = current_timestamp 
order by id desc
limit 1

edited Jan 07 '19 at 16:42

answered Jan 07 '19 at 16:40

juergen d

Shoudln't that be `current_timestamp()` or does that function work without the `()` – RiggsFolly Jan 07 '19 at 16:41
worked like a charm. Thanks mate – Adam Dominiak Jan 07 '19 at 16:42
@RiggsFolly: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_current-timestamp – juergen d Jan 07 '19 at 16:42
Juergen Thank you. – RiggsFolly Jan 07 '19 at 16:43

1 Answers1