delete duplicate rows

Question

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)

Apologies, but I don't understand the question. Can you clarify "by writing new way from script below"? Also, do you want to do this only once, or on an on going basis, if on going, can you modify the schema and add an identity column? — Binary Worrier, Jun 29 '09 at 08:55

score 3 · Answer 1 · answered Jun 29 '09 at 08:54

3

Delete Duplicate Rows in a SQL Table :

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

answered Jun 29 '09 at 08:54

Moayad Mardini

7,271
5
41
58

http://stackoverflow.com/questions/529098/removing-duplicate-rows-from-table-in-oracle – Sai Sherlekar Aug 29 '11 at 07:04
There is no rowid in sql-server! – Nashev Apr 06 '18 at 08:39

score 1 · Answer 2 · answered Jun 29 '09 at 08:50

1

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.

answered Jun 29 '09 at 08:50

odiseh

25,407
33
108
151

score 0 · Answer 3 · answered Jun 29 '09 at 08:55

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

score 0 · Answer 4 · answered Jun 29 '09 at 08:56

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).

delete duplicate rows

4 Answers4