Re: How to clean up duplicate records

> delete from foo x
> where x.rowid <> (select min(rowid) from foo y
> where y.primary key = x.primary key)

If the table is quite big (a few millions of lines for example), it will be quicker in general to get a text file using a select column1 ||'#'||column2 ... and spooling the result (I insist on spooling and not redirecting the screen output, as this adds some very annoying tabulations everywhere under solaris at least).

Then use sort -u -k on the file and reload it with sqlloader using the TRUNCATE option.
Of course, the service has to be interrupted during the operation.

This little trick saved my bacon more than once when people tend to be cheap on the processing power of the machine supporting a db with a lot of storage.

(off topic : it's simpler with sybase as the bcp utility dumps asci files of the table)

HTH,
JG Received on Fri May 18 2001 - 02:52:53 CDT