UPDATE where the timestamp is the last

Question

I have a group of records with the same data except the timestamp (yes, not my design)

Example:

record_id, user, tmstmp
1, myself, 2006-11-15 09:56:14.325882-05
1, myself, 2006-11-15 09:56:19.051823-05
1, myself, 2006-11-15 11:23:30.581366-05
etc...

Now I would like to UPDATE the record with the last timestamp. Here is what I'm trying so far with no luck:

UPDATE tbl
SET user = 'TESTING'
WHERE record_id = 1
ORDER BY tmstmp DESC
LIMIT 1

ORDER BY throws a syntax error.

I think this should be an AND condition, but not seeing how to do it. Any thoughts?

PostgreSQL is my db.

Answer 1

UPDATE tbl SET user = 'TESTING'
WHERE record_id = 1
AND tms_tmp in (select max(tms_tmp) from tbl where record_id = 1)

Answer 2

UPDATE  mytable
SET     user = 'TESTING'
WHERE   ctid =
        (
        SELECT  ctid
        FROM    mytable
        WHERE   record_id = 1
        ORDER BY
                tmstmp DESC
        LIMIT 1
        )

This correctly handles duplicates on tmstmp, if any.

Answer 3

Using PL / pgsql:

DECLARE
  cur CURSOR(key int) FOR
    SELECT * FROM tbl WHERE tbl.record_id = key ORDER BY tmstmp DESC FOR UPDATE;
BEGIN
  OPEN cur(1); -- record_id
  MOVE NEXT FROM cur;
  UPDATE tbl SET "user" = 'TESTING' WHERE CURRENT OF cur;
  CLOSE cur;
END

You can do this in the DO block (starting from version 9.0) or using a function.