Path: utzoo!censor!geac!torsqnt!news-server.csri.toronto.edu!clyde.concordia.ca!thunder.mcrcim.mcgill.edu!snorkelwacker.mit.edu!bloom-beacon!eru!hagbard!sunic!kps!ded
From: ded@kps.UUCP (David Edwards /DP)
Newsgroups: comp.databases
Subject: Re: Duplicated tuples problem (Oracle/SQL)
Message-ID: <946@kps.UUCP>
Date: 7 Jan 91 10:36:28 GMT
References: <772@keele.keele.ac.uk> <CIMSHOP!DAVIDM.90Dec21095239@uunet.UU.NET> <12224@sybase.sybase.com> <775@keele.keele.ac.uk>
Reply-To: ded@kps.UUCP (David Edwards /DP)
Organization: Kuwait Petroleum, Stockholm, Sweden
Lines: 31

In article <775@keele.keele.ac.uk> csa18@seq1.kl.ac.uk (R.J. Husmo) writes:
>In article <12224@sybase.sybase.com> you write:
>>In article <CIMSHOP!DAVIDM.90Dec21095239@uunet.UU.NET> cimshop!davidm@uunet.UU.NET (David S. Masterson) writes:
>>>>>>>> On 21 Dec 90 10:13:14 GMT, csa18@seq1.keele.ac.uk (R.J. Husmo) said:
>>>
>>>R.J.> Q: Is there a way to automatically delete duplicate tuples from a
>>>R.J.> table/all tables in a db using SQL?

Why not do the following:

Suppose F1 is the field that has the undesired duplicates in table T1.

1) Lock the table:

	select F1 from T1 X
	where rowid > (select min(rowid) from T1 where F1=X.F1)
	for update

2) delete 'em:

	delete from T1 X
	where rowid > (select min(rowid) from T1
		       where T1=X.T1)

I think that solves the problem. You can put an index on T1 to make it
go faster.  At the risk of stating the obvious put a unique index on it
after the deletion.

David Edwards
Kuwait Petroleum Sweden AB
Stockholm Sweden