X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 869244753A1
	for <pgsql-sql@postgresql.org>; Mon, 10 Feb 2003 02:11:57 -0500 (EST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id 13FD6474E5C
	for <pgsql-sql@postgresql.org>; Mon, 10 Feb 2003 02:11:57 -0500 (EST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 057D6D60D; Sun,  9 Feb 2003 23:11:52 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id EF5B55C02; Sun,  9 Feb 2003 23:11:52 -0800 (PST)
Date: Sun, 9 Feb 2003 23:11:52 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Abdul Wahab Dahalan <wahab@mimos.my>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: How to delete duplicate record
In-Reply-To: <3E4704EA.A7A813CC@mimos.my>
Message-ID: <20030209231117.H93398-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/186
X-Sequence-Number: 11584

On Mon, 10 Feb 2003, Abdul Wahab Dahalan wrote:

> How do I delete a duplicated records?
> Here I've 7 duplicated records and tried to delete 6 from them.
>
> I tried this query but has error
> b2b=> delete from biztypes where bizid = (select bizid from biztypes
> where bizid = 'B116' limit 6);
> ERROR:  More than one tuple returned by a subselect used as an
> expression.
>
> I tried this query, but all records are deleted.
> b2b=> delete from biztypes where bizid = (select bizid from biztypes
> where bizid = 'B116' limit 1);
> DELETE 7


Right, because the subselect ends up being an expensive way of writing the
constant 'B116'.  You may want to use a system column like ctid to
differentiate them, maybe:

delete from biztypes where ctid in (select ctid from biztypes where
bizid='B116' limit 6);