Sunday, April 19, 2009

DBMS: Remove Duplicate Entries from Database Table in Oracel / MS SQl

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

No comments:

Post a Comment