Delete Duplicate rows from database tables
Method 1.
This is simple method to remove duplicate entries from database tables. You can use it in oracle as well as MS SQL. Duplicate entries in aspect of one column or more than one column. Here in this query 'id' can be primary key or any other unique data, which identifies each row uniquely.
Note : I tested this query it works good, its very simple and easy way to delete duplicate rows from tables.
DELETE
FROM TableName
WHERE id NOT IN
( SELECT MAX(id)
FROM TableName
GROUP BY DuplicateColumName1, DuplicateColumName2)
Method 2.
This is Another simple method to remove duplicate entries from database tables. Works well with both Oracle and MS SQL.
Step 1: Move the non duplicates (unique rows) into a temporary table
Oracle Method to move non duplicates rows into new table from old table
CREATE TABLE NewTable AS
SELECT * FROM OldTable
WHERE 1
GROUP BY DuplicateColumName1, DuplicateColumName2
MS SQL Method to move non duplicates rows into new table from old table
SELECT * INTO NewTable
FROM OldTable
WHERE 1
GROUP BY DuplicateColumName1, DuplicateColumName2
Step 2: Delete old table.
We no longer need the table with all the duplicate entries, so drop it!
DROP TABLE OldTable
Step 3: Rename the New Table to the name of the Old Table
Oracle Method to Rename tables
RENAME TABLE NewTable TO OldTable
MS SQL Method to Rename tables
ALTER TABLE OldTable RENAME TO NewTABLE
