Re: Delete the duplicated ones

> I have tried with generic standard syntax to delete the duplicated
> rows. But something wrong here, that I did not delete correct number of
> duplicated row.
> example:
> with the table T( col1 varchar2(5), col2 varchar(2));
> If I used the query of
> delete from T t1
> where t1.rowid <> <-- or use ">"
> ( select min(t2.rowID) from T t2
> where t1.col1 = t2.col1
> and t1.col2 = t2.col2) )
> for original table T, I got 0 row delete. If I have 5 rows duplicated
> I should have 5 rows deleted. However, with known 5 duplicated, I got
> more than 5 rows deleted. This is on my 8.1.6 version on a NT 4 with
> one 550 MHz CPU. My table has 27 columns, one is an object type, some
> of the columns may have NULL value. And I did use the reference to
> compare the t1.referenced.variables of the object type. There was no
> duplicated row in the original table. Anyone has a good suggestion.

You cannot compare null values with = . As for the object type, why don't you post its corresponding create ... statements and some sample data. Lastly, but nevertheless most importantly, search groups.google.com for it. I am quite convinced this won't let you in the dark.

hth

Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html