|
Subject:
|
Simply Deleting Question - ThnX !
|
|
Posted By:
|
ank2go
|
Post Date:
|
11/16/2004 3:18:38 PM
|
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 !
|
|
Reply By:
|
ank2go
|
Reply Date:
|
11/16/2004 3:20:37 PM
|
Opps. Forgot to mention that this is for MS SQL 2K.
|
|
Reply By:
|
ank2go
|
Reply Date:
|
11/16/2004 3:55:55 PM
|
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
|
