Menu
I'm trying to update the latest record where name is John (John has multiple records but different ID) but I seem to be in a bind. What's wrong with my query?
UPDATE messages_tbl SET is_unread=1 WHERE ReceiveTime = (SELECT MAX(ReceiveTime) FROM messages_tbl WHERE name='John') Is there a better way to do something like this?
367
MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated.
To get the last updated record in SQL Server: We can write trigger (which automatically fires) i.e. whenever there is a change (update) that occurs on a row, the “lastupdatedby” column value should get updated by the current timestamp.
To get the last record, the following is the query. mysql> select *from getLastRecord ORDER BY id DESC LIMIT 1; The following is the output. The above output shows that we have fetched the last record, with Id 4 and Name Carol.
You could try using ORDER and LIMIT.
Try this:
UPDATE messages_tbl SET is_unread = 1 WHERE name = 'John' ORDER BY ReceiveTime DESC LIMIT 1 This query will update the rows in order of the highest (most recent) ReceiveTime to the lowest (oldest) ReceiveTime. Used in conjunction with LIMIT, only the most recent ReceiveTime will be altered.
59
You can join both and perform update based on the condition.
UPDATE messages a INNER JOIN ( SELECT name , MAX(ReceiveTime) max_time FROM messages GROUP BY name ) b ON a.name = b.name AND a.ReceiveTime = b.max_time SET a.is_unread = 1 -- WHERE a.name = 'John' Without the WHERE condition. It will all update the column is_unread for the latest entry.
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
