Delete duplicates from table

Question

How can I delete duplicates from a table? I know there are a lot of questions like this, but on all there is a unique column, and in my case I don't have a unique column. Basically, my table looks like:

Name | LastName | someMoreData
aaa  |   bbb    |    ccc
ddd  |   eee    |    fff
aaa  |   bbb    |    ccc

and I want to delete one of the two identical rows.

is there anything that makes each row unique, or are you saying you can only determine if it's a duplicate by looking at the values in all of the columns? — Beth, Sep 22 '15 at 15:08
possible duplicate of [How to delete duplicates on a MySQL table?](http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table) — Norbert, Sep 22 '15 at 15:09
Flagged as duplicate: There are multiple solutions on StackOverflow for this. — Norbert, Sep 22 '15 at 15:10
@NorbertvanNobelen I agree this has been asked a million times but the one you linked is for mysql and this is tagged sql server. — Sean Lange, Sep 22 '15 at 15:18
possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Sean Lange, Sep 22 '15 at 15:19
It is the same logic and MySQL is usually ANSI SQL, which will run just fine in SQL Server. You only have to add a unique column (temporary) to your table with a sequential number so you can delete single records. — Norbert, Sep 22 '15 at 15:45

score 2 · Accepted Answer · answered Sep 22 '15 at 19:55

2

you could do something like this

with x as   (select  Name , LastName , someMoreData,rn = row_number()
        over(PARTITION BY Name , LastName , someMoreData order by Name
  )
        FROM yourtable)
   delete x where rn > 1

answered Sep 22 '15 at 19:55

Birby

331
1
3
8

thanks, works like a charm!:) Never thought about adding row_number to have a unike identifier – next_user Sep 23 '15 at 07:37

Dzmitry Paliakou · Answer 2 · 2015-09-22T16:04:22.813

Without details about identity and order and according only to your example, I can propose this solution:

SELECT DISTINCT * INTO #tmp FROM your_table;
GO
TRUNCATE TABLE your_table;
GO
INSERT your_table
SELECT * FROM #tmp
GO
DROP TABLE #tmp

select unique rows from a basic table into a temporary table #tmp
delete all data from the basic table
move data from the temporary table to the basic table
delete the temporary table

score 0 · Answer 3 · answered Sep 22 '15 at 16:27

Try below solution and check this SQLFiddle

Stage 1: Create a temporary table having distinct data. You can use any of the below query.

Assumption: ##t1 is your TableName

select * into ##t2 from (select Distinct * from ##t1) as T

or

SELECT DISTINCT * INTO ##t2 FROM ##t1

Stage 2: Clear-up your existing table. So may either drop/truncate the table and re-create it, or another way is just to delete the data that is in the table because you don't need that anymore.

drop table ##t1

or 

Truncate table ##t1

or

delete from ##t1

Stage 3: Insert data back into original table. Remember if you have opted to drop/truncate then you have to use Query1 else use Query2.

Query1

select * into ##t1 from (select * from ##t2) as t

Query2

insert ##t1 select * from ##t2

Note: When you perform drop/truncate then you lose the table structure as well, and via Query1 above, we are creating structure as well.

Delete duplicates from table

3 Answers3