Bruce Sorge wrote: > I have a lot of duplicate information in a table. I know how to query to > find the dupes, but I am having problems with deleting them (there are > thousands). I tried this: > > DELETE > FROM CODES > WHERE Code = > (SELECT Code, > COUNT(Code) AS NumOccurrences > FROM Codes > GROUP BY Code > HAVING ( COUNT(Code) > 1 )) > > Now of of course even if this one did work, it just occured to me that it > will not do what I want it to. What I want is to leave one instance of the > code in the database and delete the duplicates only. Is this possible > without me going line by line? >
Is Code the only field? One way would be something like this... SELECT DISTINCT Code INTO #tempcodetable FROM Codes DELETE FROM Codes INSERT INTO Codes SELECT Code FROM #tempcodetable DROP #tempcodetable And of course, make a backup before attempting any operation like this. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268337 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
