CODEWITHSUNDEEP
sql-server-2008
aristianto
aristianto

Reputation: 11

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

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?

Upvotes: 1

Views: 860

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 1

scarpacci
scarpacci

Reputation: 9194

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:

  1. Load all of the data from the source table you show above into a new table with an identify field.

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

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

Upvotes: 0

Related Questions