How to Delete duplicate record

All Replies

• Saturday, November 24, 2012 8:19 AM

  Moderator

   

   
  

  0

  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
  •  
  • Reply
  • Quote

   
• Saturday, November 24, 2012 10:29 AM

   

   
  

  0

  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

  • Reply
  • Quote

   
• Saturday, November 24, 2012 12:39 PM

   

   
  

  0

  In your case merge statement helps you lot.

  for more about merge see below urls

  http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/

  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.

  • Reply
  • Quote

   
• Saturday, November 24, 2012 12:43 PM

   

   
  

  0

  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
  •  
  • Reply
  • Quote

   
• Sunday, November 25, 2012 7:40 AM

   

   
  

  0
  Can tell me why you use global temp table?
  • Reply
  • Quote

   
• Sunday, November 25, 2012 8:17 AM

  Answerer

   

   
  

  0
  Please 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
  

  • Reply
  • Quote