Update first row in a set of many duplicates [MS ACESS QUERY]

Question

I'm trying to update my table, and modify the status of only the latest record in MS Access

So I have something like this in my table

 
   field1   field2   timestamp       Status

    123        4     03/18/2022        a
    123        4     03/17/2022        a
    124        5     03/17/2022        a
    124        5     03/18/2022        a

and so on...

The only thing that may differentiate them is the timestamp. I want to update only the latest record and set its status depending on that

I tried to approach using:

Group by field1, field2 order by timestamp

But I do not know how to mix it up with an Update statement.

Thanks in advance

Answer 1

You can try to use UPDATE .... JOIN with a subquery that get the latest record grouping by your expectations.

UPDATE t1 
SET    t1.Status = 'update status'
FROM   T AS t1 
INNER JOIN (SELECT field1, 
                  field2,
                  MAX(timestamp) max_timestamp
           FROM   T
           GROUP BY  field1, 
                  field2) AS t2
       ON t1.timestamp = t2.max_timestamp
       AND t1.field1 = t2.field1
       AND t1.field2 = t2.field2