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

[lfmn]

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

[James May]

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