Oracle 21c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE atable (id, value) AS
SELECT LEVEL + 0, 1 FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT LEVEL + 3, 2 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT LEVEL + 4, 3 FROM DUAL CONNECT BY LEVEL <= 2;
6 rows affected

SELECT * FROM atable;


ID
VALUE


1
1

2
1

3
1

4
2

5
3

6
3


delete from ATABLE A1
where exists (select * from ATABLE A2
where A1.VALUE = A2.VALUE and A1.ID <> A2.ID);
5 rows affected

SELECT * FROM atable;


ID
VALUE


4
2