pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: How to delete duplicate record
5+ messages / 4 participants
[nested] [flat]
* How to delete duplicate record
@ 2003-02-10 01:48 Abdul Wahab Dahalan <wahab@mimos.my>
2003-02-09 23:17 ` Re: How to delete duplicate record Tomasz Myrta <jasiek@klaster.net>
2003-02-10 00:36 ` Re: How to delete duplicate record Tomasz Myrta <jasiek@klaster.net>
2003-02-10 07:11 ` Re: How to delete duplicate record Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 3 replies; 5+ messages in thread
From: Abdul Wahab Dahalan @ 2003-02-10 01:48 UTC (permalink / raw)
To: pgsql-sql
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
Any help are very much appreciated..
Thanks. Wahab
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to delete duplicate record
2003-02-10 01:48 How to delete duplicate record Abdul Wahab Dahalan <wahab@mimos.my>
@ 2003-02-09 23:17 ` Tomasz Myrta <jasiek@klaster.net>
2 siblings, 0 replies; 5+ messages in thread
From: Tomasz Myrta @ 2003-02-09 23:17 UTC (permalink / raw)
To: wahab@mimos.my; pgsql-sql
> 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
>
> Any help are very much appreciated..
> Thanks. Wahab
Try this:
delete from biztypes where exists (select * from biztypes b2 where
biztypes.bizid=b2.bizid)
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to delete duplicate record
2003-02-10 01:48 How to delete duplicate record Abdul Wahab Dahalan <wahab@mimos.my>
@ 2003-02-10 00:36 ` Tomasz Myrta <jasiek@klaster.net>
2 siblings, 0 replies; 5+ messages in thread
From: Tomasz Myrta @ 2003-02-10 00:36 UTC (permalink / raw)
To: wahab@mimos.my; pgsql-sql
> b2b=> select * from biztypes;
> bizid | biztype
> -------+-----------
> B11 | logistics
> B11 | logistics
> B11 | logistics
> B11 | logistics
> B11 | logistics
> (5 rows)
>
> b2b=>delete from biztypes where exists (select * from biztypes b2 where
> biztypes.bizid=b2.bizid)
>
> DELETE 5
> b2bscm=> select * from test1;
> bizid | biztype
> -------+---------
> (0 rows)
>
> all the records been deleted.
> the result should be :
>
> bizid | biztype
> -------+-----------
> B11 | logistics
>
Sorry, I forgot this code works only if you have any field which is
primary key or any other unique identifier - for example oid.
It will look then:
delete from biztypes where exists (select * from biztypes b2 where
biztypes.bizid=b2.bizid and biztypes.oid<>b2.oid)
If you don't have any unique identifier for rows in your table, the only
way to delete this record is creating a copy of this table:
create table some_copy as
select
bizid,biztype
from biztypes group by bizid,biztype;
drop table biztypes;
alter table some_copy rename to biztypes;
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to delete duplicate record
2003-02-10 01:48 How to delete duplicate record Abdul Wahab Dahalan <wahab@mimos.my>
@ 2003-02-10 07:11 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-02-10 14:44 ` Re: How to delete duplicate record greg@turnstep.com
2 siblings, 1 reply; 5+ messages in thread
From: Stephan Szabo @ 2003-02-10 07:11 UTC (permalink / raw)
To: Abdul Wahab Dahalan <wahab@mimos.my>; +Cc: 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);
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to delete duplicate record
2003-02-10 01:48 How to delete duplicate record Abdul Wahab Dahalan <wahab@mimos.my>
2003-02-10 07:11 ` Re: How to delete duplicate record Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-02-10 14:44 ` greg@turnstep.com
0 siblings, 0 replies; 5+ messages in thread
From: greg@turnstep.com @ 2003-02-10 14:44 UTC (permalink / raw)
To: pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-02-10 14:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-10 01:48 How to delete duplicate record Abdul Wahab Dahalan <wahab@mimos.my>
2003-02-09 23:17 ` Tomasz Myrta <jasiek@klaster.net>
2003-02-10 00:36 ` Tomasz Myrta <jasiek@klaster.net>
2003-02-10 07:11 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-02-10 14:44 ` greg@turnstep.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox