Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!swrinde!elroy.jpl.nasa.gov!lll-winken!unixhub!slacvm!ian From: IAN@SLACVM.SLAC.STANFORD.EDU Newsgroups: comp.databases Subject: Re: Duplicated tuples problem (Oracle/SQL) Message-ID: <91002.094143IAN@SLACVM.SLAC.STANFORD.EDU> Date: 2 Jan 91 17:41:43 GMT References: <772@keele.keele.ac.uk> <12224@sybase.sybase.com> <775@keele.keele.ac.uk> Organization: Stanford Linear Accelerator Center Lines: 70 It was suggested that duplicate tables be created, and then an INSERT statement with SELECT DISTINCT be used. The problem was that the structure of the tables involved wasn't known, and there were quite a few of them. Why not use a statement akin to Create table nodupes as select distinct * from table_with_dupes; This way you do not have to know the structure beforehand. I also did some experimenting. I just tried this...haven't checked it out thoroughly... Let's say your table with duplicates looks like this SQL> SELECT * FROM DELTEST; COL1 COL2 ----- ----- ABCDE UVXYZ ABCDE UVXYZ ABCDE UVXYZ ABCDE UVXYZ FGHIJ QPRST FGHIJ QPRST FGHIJ QPRST and COL1 is the key to the table. Create a temporary table thus create table temp(row_id,col11) as select rowid,col1 from deltest so you now have SQL> SELECT * FROM TEMP; ROW_ID COL1 ------------------ ----- 00000940.0000.0006 ABCDE 00000940.0001.0006 ABCDE 00000940.0002.0006 ABCDE 00000940.0003.0006 ABCDE 00000940.0004.0006 FGHIJ 00000940.0005.0006 FGHIJ 00000940.0006.0006 FGHIJ 7 rows selected. obviously your values for row_id are going to differ. Now issue the following command DELETE FROM DELTEST WHERE DELTEST.COL1 IN (SELECT COL1 FROM TEMP WHERE TEMP.ROW_ID < DELTEST.ROWID) / checking the deltest table now reveals SQL> select * from deltest; COL1 COL2 ----- ----- ABCDE UVXYZ FGHIJ QPRST I much prefer the first suggestion, as the latter one requires that you know the "unique" key to the table. Ian MacGregor Stanford Linear Accelerator Center