From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Abdul Wahab Dahalan <wahab(at)mimos(dot)my> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: How to delete duplicate record |
Date: | 2003-02-10 07:11:52 |
Message-ID: | 20030209231117.H93398-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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);
From | Date | Subject | |
---|---|---|---|
Next Message | Frankie Lam | 2003-02-10 07:47:12 | Re: plpgsql + dblink() question |
Previous Message | postgresql | 2003-02-10 07:06:31 | PostgreSQL SQL Conformance |