SQL SERVER 2008 – How does one eliminate duplicates rows from a table

Choose one of the following queries to identify or remove duplicate rows from a table leaving only unique records in the table:

Method 1:

SQL> DELETE FROM table_name A WHERE ROWID > (      2    SELECT min(rowid) FROM table_name B      3    WHERE A.key_values = B.key_values);

Method 2:

SQL> create table table_name2 as select distinct * from table_name1;    SQL> drop table_name1;    SQL> rename table_name2 to table_name1;    SQL> — Remember to recreate all indexes, constraints, triggers, etc on table…

Method 3:

SQL> delete from my_table t1    SQL> where  exists (select ‘x’ from my_table t2

SQL> where t2.key_value1 = t1.key_value1

SQL> and t2.key_value2 = t1.key_value2

SQL>and t2.rowid  > t1.rowid);

Note: One can eliminate N^2 unnecessary operations by creating an index on the joined fields in the inner loop (no need to loop through the entire table on each pass by a record). This will speed-up the deletion process.

Note 2: If you are comparing NOT-NULL columns, use the NVL function. Remember that NULL is not equal to NULL. This should not be a problem as all key columns should be NOT NULL by definition.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s