RE: [CF-Database] Deleting duplicates from a database

["Phil Ewington - 43 Plc" <phil.ewington@43plc.com>]

we use this...

DELETE FROM [table]
FROM [table] unique  
	LEFT JOIN
        	(SELECT MIN([uniqueID]) AS [uniqueID]
      		FROM [table] 
      		GROUP BY [duplication]) derived 
	ON uniqueID = derived.[uniqueID]
WHERE derived.[uniqueID] IS NULL

--[table] = that contains duplicated data
--[duplication] = column(s) that are duplicated
--[uniqueID] = there must be a column that is a unique identifier


HTH

- Phil.




> -----Original Message-----
> From: database-bounces@list.cfdeveloper.co.uk
> [mailto:database-bounces@list.cfdeveloper.co.uk]On Behalf Of Paul
> Johnston
> Sent: 09 February 2005 10:53
> To: database programming
> Subject: [CF-Database] Deleting duplicates from a database
> 
> 
> 
> I have a SQL Server database and a whole load of duplicates in one table 
> based upon about 5 fields.  I can easily find out which records are 
> duplicated using HAVING COUNT(*) > 1, but I can't simply figure out how 
> to delete all but 1 of the records.
> 
> The idea currently is to copy each one into a temp table, via the GROUP 
> BY function, and then delete those in the original table, and then 
> re-insert from the temp table.
> 
> What do you think?  Is this the right way or am I missing something?
> 
> Paul
> 
> --
> For details on ALL mailing lists and for joining or leaving 
> lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo
> 
> --
> CFDeveloper Sponsors and Supporters:-
> >- Hosting provided by www.cfmxhosting.co.uk -<
> >- Lists hosted by www.Gradwell.com -<
> >- supporters:- www.apycom.com :  www.proworkflow.com -<
> >- CFdeveloper is run and managed by Russ Michaels, feel free to 
> volunteer your help -<
> 
> -- 
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.300 / Virus Database: 265.8.6 - Release Date: 07/02/2005
>  
> 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.6 - Release Date: 07/02/2005