CODEWITHSUNDEEP
sqldatabasems-accessvbams-access-2010
Mgogan
Mgogan

Reputation: 55

SQL UPDATE to update the most recent date in table

How can I change this statement to only update the most recent entry for the criteria Cat and assetnum? There will be more than one entry for these two criteria, each one will have a different "calibration date". I need to only update the calibration due date for the most recent calibration date.

DoCmd.RunSQL ("UPDATE [Calibration Data] SET [Calibration Due Date] = '" & MyNumber & "' WHERE Category = '" & Cat & "' AND [ID] = " & assetnum & "")

Needs to be changed to something like this:

DoCmd.RunSQL ("UPDATE [Calibration Data] SET [Calibration Due Date] = '" & MyNumber & "' WHERE Category = '" & Cat & "' AND [ID] = " & assetnum & " AND <max of calibration date column for given Cat and assetnum>")

Upvotes: 1

Views: 483

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

The following should work for updating the maximum date:

UPDATE [Calibration Data] as cd
    SET [Calibration Due Date] = '" & MyNumber & "'
    WHERE Category = '" & Cat & "' AND
          [ID] = " & assetnum & " AND
          not exists (select 1
                      from [Calibration Data] as cd2
                      where cd2.category = cd.category and
                            cd2.id = cd.id and
                            cd2.calibrationdate > cd.calibrationdate
                     );

The logic is to update the rows where there is no larger date for a given category and id.

Upvotes: 1

Related Questions