How can I update a column based on whether the row number is max?-CodePudding

I have a situation in MS SQL where two or more records in one table need to be compared, and the record with the greatest date needs to be set as the current record. I have copied the records to be compared to a temp table, and added a row number calculation. The temp table is called CANDIDACY and looks something like this:

        ID CUR_CAND RowNum Date
        1  N        1      2018-08-01
        1  N        2      2020-06-15
        1  Y        4      2021-07-22
        2  N        3      2021-04-08
        2  Y        5      2022-06-15

I would like to set CUR_CAND = Y where MAX(rn), and N for all other cases. I've tried setting CUR_CAND for all rows to N and then using an update statement to set the row with MAX(rn) to Y, but I can't figure out the syntax for the update statement. Any help is appreciated.

CodePudding user response：

You may also use NOT EXISTS as the following:

UPDATE T SET CUR_CAND = 'Y'
  FROM #CANDIDACY T
WHERE NOT EXISTS (SELECT 1 FROM #CANDIDACY D WHERE D.ID=T.ID AND D.Date>T.Date)

See a demo.