By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE a_job (
id NUMBER(5,0) GENERATED ALWAYS AS IDENTITY,
job_name VARCHAR2(20)
);
INSERT INTO a_job (job_name)
SELECT 'a' FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT 'b' FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 'c' FROM DUAL CONNECT BY LEVEL <= 5;
10 rows affected
INSERT INTO a_job (job_name)
SELECT 'a' FROM DUAL CONNECT BY LEVEL <= 3;
3 rows affected
COMMIT;
DELETE FROM /*RDSUSER.*/A_JOB
WHERE ROWID IN (SELECT ROWID
FROM (SELECT ROWID
, ROW_NUMBER() OVER(PARTITION BY JOB_NAME ORDER BY JOB_NAME) DUP
FROM /*RDSUSER.*/A_JOB)
WHERE DUP > 1);
10 rows affected
SELECT * FROM A_JOB;
| ID | JOB_NAME |
|---|---|
| 4 | b |
| 10 | c |
| 12 | a |
ROLLBACK;
DELETE FROM /*RDSUSER.*/A_JOB JOB
WHERE ROWID > (SELECT MIN(ROWID)
FROM A_JOB
WHERE JOB.JOB_NAME = A_JOB.JOB_NAME);
10 rows affected
SELECT * FROM A_JOB;
| ID | JOB_NAME |
|---|---|
| 1 | a |
| 4 | b |
| 6 | c |
ROLLBACK;
DELETE FROM /*RDSUSER.*/A_JOB
WHERE ROWID IN (SELECT ROWID
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY JOB_NAME ORDER BY ROWID) DUP
FROM /*RDSUSER.*/A_JOB)
WHERE DUP > 1);
10 rows affected
SELECT * FROM A_JOB;
| ID | JOB_NAME |
|---|---|
| 1 | a |
| 4 | b |
| 6 | c |