|
Find and Delete duplicate records in a table
Many times you can face problem of duplicate records in table.So How would you identify and delete duplicate records in a table?
For that Firstly check if table has duplicate records or not using below query.
SELECT [FirstName] FROM tblTest GROUP BY [FirstName] HAVING COUNT(*) > 1
Then Delete duplicate records.
DELETE FROM tblTest WHERE ID NOT IN (SELECT MAX(ID) FROM tblTest GROUP BY [FirstName])
|
User Comments:
Comment By
veeramanink
on
09/14/2010
try this then reply
/*
CREATE TABLE [dbo].[userTbl1](
[UName] [nvarchar](50) NULL,
[Email] [nvarchar](250) NOT NULL,
[sex] [nvarchar](20) NULL
)
GO
*/
/*
insert into userTbl1(Uname,Email,sex) values ('veera','veera@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('Bala','Bala@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('sakthi','sakthi@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('ganesh','ganesh@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('ramesh','ramesh@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('veera','veera@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('Bala','Bala@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('sakthi','sakthi@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('ganesh','ganesh@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('ramesh','ramesh@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('veera','veera@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('Bala','Bala@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('sakthi','sakthi@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('ganesh','ganesh@cb.in','M')
insert into userTbl1(Uname,Email,sex) values ('ramesh','ramesh@cb.in','M')
*/
/*
select * from userTbl1;
*/
/*
set rowcount 1
delete userTbl1 from userTbl1 a1 where (select count(UName) from userTbl1 a2 where a2.UName =a1.UName)>1
while @@rowcount > 0
delete userTbl1 from userTbl1 a1 where (select count(UName) from userTbl1 a2 where a2.UName =a1.UName)>1
set rowcount 0
*/
/*
select * from userTbl1;
*/
Post Your Comment :
|