Search code examples
sqlms-access-2007jet-sql

Is there a JET SQL condition clause to update only the Max Date records?

I am using this Jet SQL Syntax,

UPDATE tbl1 
   INNER JOIN tbl2 ON tbl1.123 = tbl2.123 
   SET tbl1.a = tb2.b

For my condition to update only the max dates for table one I have used iterations of:

WHERE tbl1.123 IN (SELECT tbl1.123 FROM tbl1 GROUP BY tbl1.123 HAVING MAX(date));

HAVING MAX(date);

Is it not possible to make sure I am only updating the max date records in table 1?

I have duplicate records in table 1 and need to ensure that any new data from table 2 only gets attached to the most recent record and not the older duplicate values using JET SQL in Access. Thank you

Solution

  • Try this:

    UPDATE tbl1 
   INNER JOIN tbl2 ON tbl1.123 = tbl2.123 
SET 
   tbl1.a = tb2.b
WHERE 
   tbl1.Date IN 
       (SELECT MAX(T.Date) 
       FROM tbl1 As T 
       WHERE T.123 = tbl1.123)