Re: Eliminating duplicates

In article <1994Apr19.215930.1228_at_rat.csc.calpoly.edu> mbhatt_at_galaxy.csc.calpoly.edu (Manish J. Bhatt) writes:

>Is there a way to remove duplicate records from a table? I did an
>import from a backup three times, and I didn't realize that each time
>it would add in another copy of a record. I can't go back to the
>backup because new data has been added since then.
>
>Thanks,
>
>Manish
>
>---------------------------------------------------------------------------
>Manish J. Bhatt Administrative Systems
>AIX Systems Administrator California Polytechnic State University
>mbhatt_at_dtvrut.infsys.calpoly.edu S a n L u i s O b i s p o
>

Let's say you have a table:

TEST

to delete one of the exact duplicate rows you could use the following code:

DELETE from TEST

		WHERE rowid > 
	(select min(rowid)
		from test outer
	WHERE	(col1 = outer.col1	and
			col2  = outer.col2	and
			col3  = outer.col3);

good luck,

milly Received on Wed Apr 20 1994 - 19:37:07 CEST