how to update a table based on select statement in oracle

Question

hello i have a table has this data

T1:
Id start_date end_date flag
1  11-Jan-20  11-Jan-20 Y
3  11-Jan-20  11-Jan-20 Y
2  11-Jan-20  11-Jan-20 Y
3  11-Mar-21  11-Jan-20 Y

what i want is to get the older start dates foreach id and make their flag to N i want my output to be like that

T1:
Id start_date end_date flag
1  11-Jan-20  11-Jan-20 Y
3  11-Jan-20  11-Jan-20 N
2  11-Jan-20  11-Jan-20 Y
3  11-Mar-21  11-Jan-20 Y

i tried this sql statement

update
T1
set
flag='N'
where
Id
in
(select *
from T1
where start_date not in
(select MAX(start_date)as maxm from T1 group by T1.id) and Flag='Y');

but the problem is it takes all records contain id and update them to N it gaves me this output

T1:
Id start_date end_date flag
1  11-Jan-20  11-Jan-20 Y
3  11-Jan-20  11-Jan-20 N
2  11-Jan-20  11-Jan-20 Y
3  11-Mar-21  11-Jan-20 N

Answer 1

One option is to use merge.

Before:

SQL> select * from t1 order by id, start_date;

        ID START_DAT END_DATE  F
---------- --------- --------- -
         1 11-Jan-20 11-Jan-20 Y
         2 11-Jan-20 11-Jan-20 Y
         3 11-Jan-20 11-Jan-20 Y
         3 11-Jan-20 11-Jan-20 Y
         3 11-Mar-21 11-Jan-20 Y

Update flag:

SQL> merge into t1 a
  2  using (select b.id,
  3                b.start_date,
  4                row_number() over (partition by b.id order by b.start_date desc) rn
  5         from (select distinct t.id,
  6                               t.start_date
  7               from t1 t
  8              ) b
  9        ) x
 10  on (a.id = x.id and
 11      a.start_date = x.start_date
 12     )
 13  when matched then update set
 14    a.flag = 'N'
 15    where x.rn > 1;

2 rows merged.

After:

SQL> select * from t1 order by id, start_date;

        ID START_DAT END_DATE  F
---------- --------- --------- -
         1 11-Jan-20 11-Jan-20 Y
         2 11-Jan-20 11-Jan-20 Y
         3 11-Jan-20 11-Jan-20 N
         3 11-Jan-20 11-Jan-20 N
         3 11-Mar-21 11-Jan-20 Y