How to Delete duplicate record
-
Saturday, November 24, 2012 8:06 AM
Hi All,
I want to write a script to delete those record from table1 and table2 which have duplicate in table1.col1 table2.col2,table2.col3.please share your experiences.
Thanks and Regards,
Mukesh Singh
All Replies
-
Saturday, November 24, 2012 8:19 AMModerator
So "motorbike" can be duplicated in table1.col1 table2.col2,table2.col3?
Which one do you want to keep?
Duplicate removal article:
http://www.sqlusa.com/bestpractices/eliminateduplicates/
Kalman Toth SQL 2008 GRAND SLAM
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012- Marked As Answer by Naomi NMicrosoft Community Contributor, Moderator Friday, November 30, 2012 4:15 PM
-
Saturday, November 24, 2012 10:29 AM
Try
create ##tmp1 as select a.col1,row_number() over (partition by col1) rn
from table1 a
where rn=1;
truncate table table1;
insert into table1(col1) select col1
from ##tmp1;
create ##tmp2 as select a.col2,col3,row_number() over (partition by col2,col3) rn
from table2 a
where rn=1;
truncate table table2;
insert into table2(col1) select col1
from ##tmp2;
and please add other relevant columns for both table1 and table2 in above scripts.
Many Thanks & Best Regards, Hua Min
-
Saturday, November 24, 2012 12:39 PM
In your case merge statement helps you lot.
for more about merge see below urls
http://technet.microsoft.com/en-us/library/bb510625.aspx
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
-
Saturday, November 24, 2012 12:43 PM
Hi,
Way of Microsoft support team :
http://support.microsoft.com/kb/139444?wa=wsignin1.0
Using Cursor :
declare @ID varchar(100) declare @Count int declare CursorDuplicates Cursor for SELECT EmployeeNo FROM tblEmployee open CursorDuplicates fetch next from CursorDuplicates into @ID while @@fetch_status=0 begin select @Count = count(EmployeeNo) from tblEmployee where EmployeeNo = @ID if @Count > 1 begin DELETE tblEmployee WHERE CURRENT OF CursorDuplicates end fetch next from CursorDuplicates into @ID end close CursorDuplicates deallocate CursorDuplicates
another way :
WITH CTE AS ( SELECT *,RN=ROW_NUMBER() OVER (PARTITION BY id ORDER BY id DESC) FROM sampletable ) DELETE FROM CTE WHERE RN > 1
Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/
- Edited by Ahsan Kabir Saturday, November 24, 2012 12:54 PM
- Proposed As Answer by Albeart Leaon Sunday, November 25, 2012 7:39 AM
-
Sunday, November 25, 2012 7:40 AMCan tell me why you use global temp table?
-
Sunday, November 25, 2012 8:17 AMAnswererPlease post table's structure PK+FK+ sample data + desired result.
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

