How to delete
duplicate row in Sql Server?
Here i use only name column
select * from
student1 order by
name,age
id name age
----------- --------------------- -----------
7 Ramu
Kumar 30
14 Sachi
Supriya 25
18 Sachi 28
19 Ramu 29
20 ramu 30
22 ramu 29
insert into student1(name,age) values('ramu',29)
id name age
----------- ------------------------ -----------
19 Ramu 29
22 ramu 29
23 ramu 29
20 ramu 30
7 Ramu
Kumar 30
18 Sachi 28
14 Sachi
Supriya 25
WITH tblDuplicate as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name ORDER BY Name)
As RowNumber,* FROM student1
)
SELECT * FROM
tblDuplicate
RowNumber id name
age
------- -------
----------- ------ --------
1 19 Ramu
29
2 20 ramu
30
3 22 ramu 29
4 23 ramu
29
1 7 Ramu Kumar 30
1 18 Sachi
28
1 14 Sachi Supriya 25
WITH tblDuplicate as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name ORDER BY Name)
As RowNumber,* FROM student1
)
DELETE FROM tblDuplicate where RowNumber >1
(3 row(s) affected). its mean deleted
Now No duplicate
row found ...
WITH tblDuplicate as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name ORDER BY Name)
As RowNumber,* FROM student1
)
SELECT *
FROM tblDuplicate
RowNumber id
name age
------------- ------ -------------------------
1 19
Ramu 29
1 7
Ramu Kumar 30
1 18
Sachi 28
1 14
Sachi Supriya 25
0 comments:
Post a Comment