1

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

Dre Day
  • 338
  • 2
  • 8

1 Answers1

1

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)
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • 1
    This worked - thank you. I guess explicitly making the connection in the subquery using WHERE helps it understand better? – Dre Day Nov 07 '22 at 14:25