|
|
|
|
Find and remove duplicate values within a table.
|
| |
|
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
- You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
- You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
- You may link to this code from another website, but ONLY if it is not wrapped in a frame.
- You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
|
--**************************************
-- Name: Find and remove Dupes
-- Description:Find and remove duplicate values within a table.
-- By: Lewis E. Moten III
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=404&lngWId=5--for details.--**************************************
DECLARE @MyValue VARCHAR(50)
-- Define cursor
DECLARE Dupes CURSOR FOR
SELECT [MyField] FROM [MyTable] GROUP BY [MyField]
HAVING COUNT(0) > 1 ORDER BY [MyField] ASC
-- Open connection to records
OPEN Dupes
-- Grab first record
FETCH NEXT FROM Dupes INTO @MyValue
-- While we have a record
WHILE @@FETCH_STATUS = 0
BEGIN
-- Delete extra dupes
/*
We find 1st values id, then delete all duplicate
values that do not have same ID
*/
DELETE FROM [MyTable] WHERE [MyRecordID] IN(SELECT [MyRecordID]
FROM [MyTable] WHERE [MyField] = @MyValue AND [MyRecorID] NOT
IN(SELECT TOP 1 [MyRecordID] FROM [MyTable] WHERE [MyField] =
@MyValue))
-- move to next record
FETCH NEXT FROM Dupes INTO @MyValue
END
-- close the connection
CLOSE Dupes
-- remove cursor
DEALLOCATE Dupes
|
|
Other 31 submission(s) by this author
|
|
Report Bad Submission
|
Your Vote
|
| |
Other User Comments
|
 8/13/2002 2:57:32 AM: Simon
Lewis, why not bypass the whole cursor thing and use something like:
DELETE FROM [MyTable] WHERE [MyRecordID] NOT IN(
SELECT Max([MyRecordID]) FROM [MyTable] WHERE [MyField] = @MyValue
GROUP BY [MyField] HAVING COUNT([MyField]) > 1
)
(If this comment was disrespectful, please report it.)
|
8/13/2002 9:33:38 AM: Rasputin
Lewis and Simon,
Regardless of which approach you take, my suggestion would be that, for the sake of better readability, you both refrain from the use of unnecessary column name bracketing. The larger the SQL statement, the more frustrating it becomes to have to ignore all the extra characters that serve no real purpose.
Respectfully,
- Ras
(If this comment was disrespectful, please report it.)
|
8/13/2002 9:49:48 AM: Simon
I agree! I think they were just there to emphasise the fact that those field names should be changed when using the script.
:)
(If this comment was disrespectful, please report it.)
|
8/13/2002 11:36:56 AM: Rasputin
...makes sense!
- Ras
(If this comment was disrespectful, please report it.)
|
8/16/2002 8:29:20 AM: Charles Kincaid
The brackets are needed if there are spaces in the field or table names. The other problem is when you have full security on and have to specifiy table, or (gasp) column, owner.
(If this comment was disrespectful, please report it.)
|
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular code, please
click here instead.)
To post feedback, first please login.
|
By 6 Users
