how to delete duplicate rows in sql?

Replies to This Discussion

Permalink Reply by k.vijay on December 15, 2011 at 10:53am

Hi,

 

 

input

name John Smith John Smith Smith Tom 

CREATE TABLE Ttemp(name VARCHAR(50) NOT NULL PRIMARY KEY);
GO
INSERT INTO Ttemp
SELECT DISTINCT * FROM T;
GO
DROP TABLE T;
GO
EXEC sp_rename 'Ttemp', 'T';
GO
SELECT * FROM T;

out put

name John Smith Tom 

Regards,

Vijay

09916686756

• ▶ Reply

Permalink Reply by PRABU DAKSHAN on December 15, 2011 at 10:57am

select (particular ID) from tablename having count(particular ID)>2

try this u can get a solution 

by

prabu.

• ▶ Reply

Permalink Reply by chandra sekhar on December 15, 2011 at 11:03am

http://support.microsoft.com/kb/139444

• ▶ Reply

Permalink Reply by TULASI on December 15, 2011 at 11:19am

Hi,

  first create a back up copy table using INTO like this

SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename.

 

i though its easy if we  count the dublicate columns first

SELECT col1, col2, col3=count(*) INTO new_table FROM old_table GROUP BY col1, col2 HAVING count(*) > 1

 

then u will get list of dublicate rows

>>copy those rows in dup_new_table

SELECT DISTINCT old_table.* INTO dup_new_table FROM old_table, new_table WHERE old_table.col1 = new_table.col1
AND old_date.col2 = new_table.col2

 

>>At this point, the dup_new_table table should have unique PKs, however, this will not be the case if old_table had duplicate PKs, yet unique rows. Verify that each key in dup_new_table is unique, and that you do not have duplicate keys, yet unique rows. If so, you must stop here and reconcile which of the rows you wish to keep for a given duplicate key value. For example, the query:

 

SELECT col1, col2, count(*) FROMdup_new_table GROUP BY col1, col2

 

>>should return a count of 1 for each row. If yes, proceedbelow query. If no, you have duplicate keys, yet unique rows, and need to decide which rows to save. This will usually entail either discarding a row, or creating a new unique key value for this row. Take one of these two steps for each such duplicate PK in the dup_new_table table.

 

DELETE old_table FROM old_table, new_table WHERE old_table.col1 = new_table.col1 AND old_table.col2 = new_table.col2

>>Put the unique rows back in the original table

INSERT old_table SELECT * FROM dup_new_table

 

>>if there any changes or clarification please let me know

thanks for understanding

 

Thanks

Tulasi

• ▶ Reply