X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CBBFC4753A1 for ; Mon, 10 Feb 2003 09:44:11 -0500 (EST) Received: from biglumber.com (biglumber.com [207.228.252.42]) by postgresql.org (Postfix) with SMTP id 2E744474E53 for ; Mon, 10 Feb 2003 09:44:11 -0500 (EST) Received: (qmail 27098 invoked from network); 10 Feb 2003 14:44:10 -0000 Received: from biglumber.com (HELO localhost) (207.228.252.42) by 0 with SMTP; 10 Feb 2003 14:44:10 -0000 From: greg@turnstep.com To: pgsql-sql@postgresql.org Subject: Re: How to delete duplicate record X-PGP-Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8 In-Reply-To: <20030209231117.H93398-100000@megazone23.bigpanda.com> Date: Mon, 10 Feb 2003 14:44:10 -0000 X-Mailer: JoyMail 1.36 Message-ID: X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/191 X-Sequence-Number: 11589 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Abdul Wahab Dahalan: >> Here I've 7 duplicated records and tried to delete 6 from them. Stephan Szabo: > delete from biztypes where ctid in (select ctid from biztypes where > bizid='B116' limit 6); A more general form of the "delete all but one" is: DELETE FROM biztypes WHERE ctid != (SELECT ctid FROM biztypes WHERE bizid='B116' LIMIT 1); You can also usually use the oid column instead of the ctid column. System columns explained: http://www.gtsm.com/cgi/psearch?page=ddl-system-columns.html;q=ctid;c=0 - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200302100934 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE+R7oUvJuQZxSWSsgRAloaAJ9AkNjjYvCuOB8wanR8RoouEmU6eACg5YVX 3R+q3GDK5Pblu7L7z3TIEnE= =qM8Y -----END PGP SIGNATURE-----