SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Create Table #CANDIDACY (ID INT, CUR_CAND VARCHAR(50), RowNum INT, [Date] DATE);

Insert Into #CANDIDACY  Values
(1,'N',1,'2018-08-01'),
(1,'N',2,'2020-06-15'),
(1,'N',4,'2021-07-22'),
(2,'N',3,'2021-04-08'),
(2,'N',5,'2022-06-15');
5 rows affected

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)
2 rows affected

SELECT * FROM #CANDIDACY;


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

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