Saturday, November 24, 2012 8:06 AM
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,
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:
- Marked As Answer by Naomi NMicrosoft Community Contributor, Moderator Friday, November 30, 2012 4:15 PM
Saturday, November 24, 2012 10:29 AM
create ##tmp1 as select a.col1,row_number() over (partition by col1) rn
from table1 a
truncate table table1;
insert into table1(col1) select col1
create ##tmp2 as select a.col2,col3,row_number() over (partition by col2,col3) rn
from table2 a
truncate table table2;
insert into table2(col1) select col1
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
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
Way of Microsoft support team :
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/
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