remove duplicates based on three columns

Answers

• 

  

  1

  Sign in to vote

  Would this work?

  declare @tablename table(col1 int, col2 int, col3 int)
  insert into @tablename (col1, col2, col3) values(1,2,3)
  insert into @tablename (col1, col2, col3) values(4,5,6)
  insert into @tablename (col1, col2, col3) values(7,7,6)
  insert into @tablename (col1, col2, col3) values(8,8,8)
  insert into @tablename (col1, col2, col3) values(9,9,8)
  insert into @tablename (col1, col2, col3) values(9,9,9)
  insert into @tablename (col1, col2, col3) values(9,9,9)--dup
  select col1, col2, col3, count(*) from @tablename  where col1=col2 and col2=col3
  group by col1, col2, col3
  having count(*)>1
  delete from t
  from @tablename  t
  join (select col1, col2, col3, count(*) count from @tablename  where col1=col2 and col2=col3
  group by col1, col2, col3
  having count(*)>1
  ) as X
  on t.col1=X.col1
  and t.col2=X.col2
  and t.col3=X.col3

  select * from @tablename

  • Proposed as answer by Teige Gao Thursday, October 27, 2016 12:06 PM
  • Marked as answer by Ed Price - MSFTMicrosoft employee Thursday, December 15, 2016 3:31 AM

  Tuesday, October 25, 2016 2:19 AM
• 

  

  1

  Sign in to vote

  I blogged about it long time ago

  http://dimantdatabasesolutions.blogspot.co.il/2007/02/dealing-with-duplicates.html

  ---

  Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
  
  MS SQL optimization: MS SQL Development and Optimization
  MS SQL Consulting: Large scale of database and data cleansing
  Remote DBA Services: Improves MS SQL Database Performance
  SQL Server Integration Services: Business Intelligence
  

  • Marked as answer by Ed Price - MSFTMicrosoft employee Thursday, December 15, 2016 3:31 AM

  Wednesday, October 26, 2016 6:45 AM

  Answerer