CODEWITHSUNDEEP
sql-server
tiny
tiny

Reputation: 183

delete duplicate rows

anyone know how can i delete duplicate rows by writing new way from script below to improve performance.

DELETE lt1 FROM #listingsTemp lt1, #listingsTemp lt2
    WHERE lt1.code = lt2.code and lt1.classification_id > lt2.classification_id and (lt1.fap < lt2.fap or lt1.fap = lt2.fap)

Upvotes: 2

Views: 523

Answers (4)

Jonathan
Jonathan

Reputation: 12025

The odiseh answer seems to be valid (+1), but if for some reason you can't alter the structure of the table (because you have not the code of the applications that are using it or something) you could write a job that run every night and delete the duplicates (using the Moayad Mardini code).

Upvotes: 0

Chris Klepeis
Chris Klepeis

Reputation: 9973

Look into BINARY_CHECKSUM .... you could possibly use it when creating your temp tables to more quickly determine if the data is the same.... for example create a new field in both temp tables storing the binary_checksum value... then just delete where those fields equal

Upvotes: 0

Moayad Mardini
Moayad Mardini

Reputation: 7341

Delete Duplicate Rows in a SQL Table :

delete table_a
where rowid not in 
(select min(rowid) from table_a 
group by column1, column2);

Upvotes: 3

odiseh
odiseh

Reputation: 26517

1 - Create an Identity Column (ID) for your table (t1) 2 - Do a Group by on your table with your conditions and get IDs of duplicated records. 3 - Now, simply Delete records from t1 where IDs IN duplicated IDs set.

Upvotes: 1

Related Questions