-
delete duplicates
I have a query that returns duplicate addresses
but am not sure to delete them
Can anyone provide some syntax insight as to how to do this?
Thanks
-
delete duplicates (reply)
This might not be the best way, but you could select a distinct and put the rows into a temp table, then delete ALL of the duplicates and insert the distint rows back into the original table.
------------
at 3/2/01 11:47:38 AM
I have a query that returns duplicate addresses
but am not sure to delete them
Can anyone provide some syntax insight as to how to do this?
Thanks
-
delete duplicates (reply)
Yep, it's the best way I know. This should work for you. I didn't test this syntax, so there might be an error, but basically this is what you want
/* Make sure TempDB has appropriate option set */
TempDB , 'select into/bulkcopy' , TRUE
/* Create a backup table of your table w/ duplicate rows */
SELECT * INTO ##BackupOfMyDupedTable FROM MyDupedTable
/* Put unique rows into a temp table */
SELECT DISTINCT * FROM MyDupedTable INTO ##MyLeanerMeanerTable
/* Confirm you now have unique rows */
SELECT Address FROM ##MyLeanerMeanerTable
GROUP BY Address
HAVING COUNT(Address) > 1
/* Whack the rows from the original table (but preserve permissions, indexes) */
TRUNCATE MyDupedTable
/* Repopulate the table which used to have duplicate rows */
INSERT INTO MyDupedTable SELECT * FROM ##MyLeanerMeanerTable
/* Check it to confirm all is well (where Address used to be a duplicated field)
SELECT Address FROM MyDupedTable
GROUP BY Address
HAVING COUNT(Address) > 1
/* If you're convinced all is well, execute the following command:
DROP ##BackupOfMyDupedTable , ##MyLeanerMeanerTable
*/
------------
lfmn at 3/2/01 4:07:17 PM
This might not be the best way, but you could select a distinct and put the rows into a temp table, then delete ALL of the duplicates and insert the distint rows back into the original table.
------------
at 3/2/01 11:47:38 AM
I have a query that returns duplicate addresses
but am not sure to delete them
Can anyone provide some syntax insight as to how to do this?
Thanks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|