how to identify and delete duplicate rows in oracle using sql 4K
In this video, you will see how to identify and delete duplicate rows in oracle.
Here are the used statements used in the demo:
CREATE TABLE item(ID NUMBER, item_pos NUMBER, DESCRIPTION VARCHAR2(50))
INSERT INTO item SELECT ROWNUM, 1+MOD(5,ROWNUM) , 'Description ' || ROWNUM FROM dual CONNECT BY LEVEL smaller than 101
SELECT * FROM item
INSERT INTO item SELECT * FROM item WHERE ID bigger than 50
SELECT ID,item_pos,DESCRIPTION,COUNT(*) FROM item GROUP BY ID,item_pos,DESCRIPTION HAVING COUNT(*) bigger than 1
SELECT * FROM item WHERE ID = 54
DELETE FROM item WHERE ROWID IN (
SELECT ROWID FROM (
SELECT ID,item_pos,DESCRIPTION, row_number() OVER(PARTITION BY ID,item_pos,DESCRIPTION ORDER BY NULL) r FROM item
) WHERE r = 2)
