Yet Another Deleting Duplicate Records (in-place)

• Peter Petrov

  SSCommitted

  Points: 1517

  More actions

  January 3, 2009 at 9:28 pm

  #199564

  Comments posted to this topic are about the item Yet Another Deleting Duplicate Records (in-place)

• odeddror

  SSC Eights!

  Points: 883

  More actions

  January 14, 2009 at 10:01 pm

  #927060

  Peter,

  You can do it with CTE like this

  WITH x As (select row_number() over (partition by id1, id2 order by id2) nr FROM MyTab)

  delete FROM x where nr > 1

  Thanks,

  Oded

• Peter Petrov

  SSCommitted

  Points: 1517

  More actions

  January 15, 2009 at 1:19 pm

  #927672

  Thanks, Oded.

  Unfortunately the CTE method does not work with SQL 2000. Last year I needed to cleanup hundreds tables in order to introduce primary keys, before 2000 - 2005 conversion and my method worked pretty well.

  Regards,

  Peter