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

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

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