update duplicate records in sql server

   In this article we will see how to update duplicate records by using Comman table expression
and Row_number() function in sql server .

Example:

step1: Create a table

         CREATE TABLE #TEMP
          (
                id int
                ,city VARCHAR(50)
              ,sNAME VARCHAR(50)

         )

step2: insert data into table

        INSERT INTO #TEMP (id,city,SNAME)VALUES(1,'sangli','SANDIPg')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(2,'sangli','SANDIPg')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(3,'sangli','SANDIPg')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(4,'sangli','SANDIPg')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(5,'sangli','SANDIPg')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(6,'kolhapur','SANDIP')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(7,'kolhapur','SANDIP')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(8,'kolhapur','SANDIP')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(9,'mumbai','SANDIP')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(10,'mumbai','SANDIP')

step3:: use below query for update a duplicate records from your table

         WITH CTE AS
                (
                          select id,city,SNAME, row_number()
                          over(partition by SNAME order byid desc)
                          duplicate_id from #TEMP
                )
       updateCTE set SNAME =null whereduplicate_id>1
       select * from#temp

explaination::
                 I have used a row_number() function for numbering a duplicate records.
for your practice you can try same query for update duplicate cities
In This article I have explain how to update duplicate records from table by using
row_number() function and comman table expression.
i hope it is helpfull to you ,if it is then give me a comments ,advice ,your questions,and
suggestion
,Thanks

Related Posts::

Comman table expression in sql server
Rank(),dence_rank(),row_number() functions in sql server
cursor in sql server