Without looking too hard, would using a TOP 1 in the sub select work? -----Original Message----- From: Bruce Sorge [mailto:[EMAIL PROTECTED] Sent: 01 February 2007 17:26 To: CF-Talk Subject: SQL QUestion
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? Thanks, -- Bruce Sorge "I'm a mawg: half man, half dog. I'm my own best friend!" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:268336 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4