Received: from oxmail.ox.ac.uk (oxmail2.ox.ac.uk [163.1.2.1]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA31376 for ; Fri, 7 Apr 2000 08:06:19 -0400 (EDT) (envelope-from moray.mcconnachie@computing-services.oxford.ac.uk) Received: from ermine.ox.ac.uk ([163.1.2.13]) by oxmail.ox.ac.uk with esmtp (Exim 3.12 #1) id 12dXWh-0000ER-00; Fri, 07 Apr 2000 13:06:15 +0100 Received: from ermine.ox.ac.uk ([163.1.2.13] helo=moraypc ident=root) by ermine.ox.ac.uk with smtp (Exim 3.13 #1) id 12dXWh-00032T-00; Fri, 07 Apr 2000 13:06:15 +0100 Message-ID: <012301bfa089$82ffa920$760e01a3@oucs.ox.ac.uk> From: "Moray McConnachie" To: "Allan Kelly" , References: <38EDB999.EA91E2DB@buildstore.co.uk> Subject: Re: duplicates Date: Fri, 7 Apr 2000 13:05:05 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Archive-Number: 200004/68 ----- Original Message ----- From: "Allan Kelly" To: Sent: Friday, April 07, 2000 11:34 AM Subject: [SQL] duplicates > I have a system bug which means that I have occasional dupicate entries in > my 'subscribers' table. I'm trying to find a query which performs something > like a 'where user_name is not unique' query. > > At the moment we use this cludge: > > select count(*), user_name from subscribers > group by user_name order by count; > > (I'm surprised I can't add 'where count > 1' but it seems conditionals on > aggregate fields are not allowed). > > This gives me a very long list with the 'not unique' entries at the bottom, eg > > count | user_name > ------+------------------ > 1 | bill.hicks > [ ..cut 9 zillion results.. ] > 1 | margaret.thatcher > 4 | linus.torvalds > 9 | bill.gates > > I then have to do > > select oid from subscribers where user_name = 'linus.torvalds'; > > and delete all but one of the records. Is there a better way to do this? DELETE FROM subscribers WHERE EXISTS(SELECT x.username FROM subscribers x WHERE subscribers.oid