How to Remove Duplicate Records From SQL Table
Syntax :
set nocount on
set rowcount
1
while 1=1
begin
delete from Table_Name
where Column_Name1 IN
(
select Column_Name1
from Table_Name
group by Column_Name1
count(*)>1
)
if @@ROWCOUNT=0
break;
end
set rowcount 0
set nocount on
set rowcount
1
while 1=1
begin
delete from Table_Name
where Column_Name1 IN
(
select Column_Name1
from Table_Name
group by Column_Name1
count(*)>1
)
if @@ROWCOUNT=0
break;
end
set rowcount 0
-- Create a table as Persons
CREATE TABLE [dbo].[Persons]
(
PersonID int ,
LastName varchar(25),
FirstName varchar(25),
Address varchar(25),
City varchar(25)
)
insert into Persons
values(1,'Billa','Ravi','Giddalur','Ongole')
insert into Persons
values(2,'A','Raju','B pata','Guntur')
insert into Persons
values(2,'A','Raju','B pata','Guntur')
insert into Persons
values(3,'B','Ranga','Rachrla','Nellor')
insert into Persons
values(4,'c','Raky','Komarolu','Cudapa')
insert into Persons
values(4,'c','Raky','Komarolu','Cudapa')
insert into Persons
values(5,'D','Roja','KSPalli','Ongole')
SELECT * FROM [Persons]
SELECT * FROM [Persons]
-- Find duplicate records
SELECT [PersonID],[LastName],[FirstName],[Address],[City],count(*) as CountOf
from [Persons]
group by [PersonID],[LastName],[FirstName],[Address],[City]
having count(*)>1
-- Delete duplicate records
SET NOCOUNT ON
SET ROWCOUNT
1
WHILE 1 = 1
BEGIN
DELETE FROM [Persons]
WHERE [PersonID] IN
(
SELECT [PersonID]
FROM [Persons]
GROUP BY [PersonID]
HAVING COUNT(*) > 1
)
IF @@Rowcount = 0
BREAK ;
END
SET ROWCOUNT 0
SELECT * FROM [Persons]
Comments
Post a Comment
Hi User,
Thanks for visiting My Blog and please provide your valuable feedback and subscribe for more updates. Please don't post any spam content or comments.
Thank You