By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (Rownumber int,OldIdassigned varchar(100),commoncode varchar(100));
insert into t values(1,'FLEX','Y2573F102'),
(2,'RCL','Y2573F102'),(3,'FLEX','Y2573F102'),
(4,'QGEN','N72482123'),(5,'QGEN','N72482123'),
(6,'QGEN','N72482123'),(7,'RACE','N72482123'),
(8,'CLB','N22717107'),(9,'CLB','N22717107'),
(10,'CLB','N22717107')
10 rows affected
delete t2
from
(select t1.*,
row_number() over(partition by commoncode order by Rownumber) rn from t t1
)t2 where rn<>1
;
7 rows affected
select * from t
| Rownumber | OldIdassigned | commoncode |
|---|---|---|
| 1 | FLEX | Y2573F102 |
| 4 | QGEN | N72482123 |
| 8 | CLB | N22717107 |