flaging duplicate without using any temp table and row_id

with TBLCTE(slno,C1,C2,C3,c4,Ranking)
AS
(
select
slno,C1,c2,c3,c4,
Ranking = DENSE_RANK() over (PARTITION BY slno,C1,C2,C3 order by slno,newID())
from trans where C4 <> 'Y'
)
update TBLCTE set C4 = 'Y' where Ranking > 1

select * from trans order by Slno,c1,C2,C3