Friday, August 2, 2019

How to delete duplicate values in table?

CREATE TABLE TEST1(N NUMBER);

INSERT INTO TEST1 VALUES(10)
INSERT INTO TEST1 VALUES(20);
INSERT INTO TEST1 VALUES(30);
INSERT INTO TEST1 VALUES(40);
INSERT INTO TEST1 VALUES(40);
INSERT INTO TEST1 VALUES(20);

COMMIT;

SELECT * FROM TEST1

10
20
30
40
40
20

SELECT *  FROM TEST1 WHERE ROWID  NOT IN (SELECT MAX(ROWID) FROM TEST1 GROUP BY N)

40
20

DELETE FROM TEST1 WHERE ROWID  NOT IN (SELECT MAX(ROWID) FROM TEST1 GROUP BY N)

2 Rows Deleted.

SELECT * FROM TEST1;

10
20
30
40


No comments:

Post a Comment

AME (Approval Management Engine)

AME (Approval Management Engine) : AME Stands for Oracle Approval Management Engine. AME is a self service web application that enables...