Update query - Find the youngest date and update another value

Question

I have an access table tblExample with these fields: ID, tripID, dates, value.

Now I am looking for an update query in SQL which achieves this: Take the records where tripID = 2. From all of them take the one which has the youngest date and set value=14.

Should be rather easy but don't get it to work. Thanks for your help.

Answer 1

You can use a subquery in the where clause to filter down to the row you want:

update tblExample
    set value = 14
    where tripID = 2 and
          dates = (select max(e2.dates)
                   from tblExample as e2
                   where e2.tripID = tblExample.tripID
                  );

Or based on id:

update tblExample
    set value = 14
    where tripID = 2 and
          id = (select top (1) e2.id
                from tblExample as e2
                where e2.tripID = tblExample.tripID
                order by e2.dates desc
               );