Re: How to delete duplicate record

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);