Thread: How to delete duplicate record
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
> 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
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);
> 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 selectbizid,biztype from biztypes group by bizid,biztype; drop table biztypes; alter table some_copy rename to biztypes; Regards, Tomasz Myrta
-----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-----