Hi,

Easy sql deleting question - I hope.

I have a duplicate record in a table, and I would only want to delete one of them.

Let's say the record is this:

perid name place thing

1 Kris HK apple
1 Kris HK apple

Thanks in advance for your help !

Opps. Forgot to mention that this is for MS SQL 2K.

I found my answer:

DECLARE dupsCsr CURSOR READ_ONLY FOR

SELECT COUNT(*), fld_Date, fld_Name, fld_Item
FROM tblMyTable
GROUP BY fld_Date, fld_Name, fld_Item
ORDER BY COUNT(*) DESC

DECLARE @emso INT --Change to match datatype on your table
DECLARE @numDups INT

OPEN dupsCsr
FETCH NEXT FROM dupsCsr INTO @emso, @numDups
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @numDups = @numDups - 1 --delete all but 1 of the duplicates
     SET ROWCOUNT @numDups
     DELETE FROM yourTable
     WHERE emso = @emso
     FETCH NEXT FROM dupsCsr INTO @emso, @numDups
END --WHILE
CLOSE dupsCsr
DEALLOCATE dupsCsr

SET ROWCOUNT 0 --restore default