Wednesday, September 22, 2010

Deleting a Single Duplicate Row

I had a coworker just ask me this and I remember having to do this a while ago.

Case: You have a table that has 2 identical rows because there are no constraints to prevent this. You want to delete one of the entries (not both).

   1:  SET COUNT 1
   2:  DELETE FROM ProductTable WHERE product_name = 'Widget Model 3000'
   3:  SET COUNT 0

It will only delete one of the products with the name 'Widget Model 3000'