How to delete rows having duplicate values using Sql Server 2008?

Question

I have to delete the duplicate values by rows so that only unique rows are left.

Suppose I have the following values in my table.

no   code       name
1    001        ronald
1    001        NULL
2    002        peter
2    002        peter
3    003        john
3    003        john
4    004        NULL
4    004        chris

I have to delete the duplicate records so that the expected output is:

no   code       name
1    001        ronald
2    002        peter
3    003        john
4    004        chris

I tried using distinct and inserting the result to temp table, but it didn't work.

Does anyone have any solutions for this?

Try adding LIMIT 1to your DELETE-Query?

mariusnn
– mariusnn

2012-08-03 02:29:14 +00:00
Commented Aug 3, 2012 at 2:29 — mariusnn
– mariusnn, Commented Aug 3, 2012 at 2:29

Community · Accepted Answer · 2021-01-18 12:32:17Z

1

delete T
from (
     select row_number() over(partition by no order by name desc) as rn
     from YourTable
     ) as T
where rn > 1

SE-Data

edited Jan 18, 2021 at 12:32

CommunityBot

11 silver badge

answered Aug 3, 2012 at 6:15

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

scarpacci · Accepted Answer · 2012-08-03 02:55:44Z

You could insert the records into a temp table with an identity field. Then delete the max / min values by the No or Code value you show above and then drop and rename the existing table using the new table you created or delete the data from the existing table and replace with the data you inserted and cleaned up in your new table.

So:

Load all of the data from the source table you show above into a new table with an identify field.
Write a delete statement using a CTE or subquery to determine the min / max or other criteria giving you back the distinct values of the rows you want removed.
Replace the data in your existing table above with your cleansed data...or drop the existing table and re-name the table you created and cleansed.

Collectives™ on Stack Overflow

How to delete rows having duplicate values using Sql Server 2008?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related