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
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
No comments:
Post a Comment
if you have any doubt any suggestions do comment