Deleting Duplicate Records

BrianWren · February 22nd, 2008, 01:19 PM

Hereâs an odd question.

Given a table with 2 col.s, DOCN & PROJ which has some duplicate rec.s, how can I delete a specific row, using SQL?

The issue that brought me to this question is a desire to delete all the duplicates, without deleting the representative record.

If I have

Code:

   12345  NULL
   12345  NULL  <ââ<<< Dupe
   12345  NULL  <ââ<<< Dupe
   23456  NULL
   34567  NULL
   34567  NULL  <ââ<<< Dupe

I want to end up with

Code:

   12345  NULL

Code:

   23456  NULL
   34567  NULL

Note that there is no ID-type column.

(Of course

Code:

DELETE FROM <table>
WHERE  DOCN = '12345'

would delete all the records having DOCN = '12345'. I want to leave one behind...)

dparsons · February 22nd, 2008, 01:30 PM

I had the same issue a while back and found this article useful:

http://www.kodyaz.com/articles/delet...n-a-table.aspx

For the record, I hate cursors but this is a situation where I don't mind using them.

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========