Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Delete the duplicated ones
Rene Nyffenegger wrote:
>
> > 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
Here is my table:
CREATE TABLE audit_po_lines
(
po_id VARCHAR2(12), po_line VARCHAR2(4), order_type VARCHAR2(12), -- 'NIFF', 'KIT' purchase_type VARCHAR2(12), buyer_id VARCHAR2(30),-- site buyer req_num VARCHAR2(14), req_suffix VARCHAR2(1), loc_id VARCHAR2(12), fsc VARCHAR2(4), niin VARCHAR2(9), nomen VARCHAR2(35), part_number VARCHAR2(35), qty NUMERIC(9,3), orig_qty NUMERIC(9,3), qty_unit VARCHAR2(2),
priority VARCHAR2(2), open_flag VARCHAR2(2), pol_remark VARCHAR2(240), order_id VARCHAR2(12), edd_date DATE,
CREATE OR REPLACE TYPE audit_T AS OBJECT (
username VARCHAR2(20), auditmode VARCHAR2(3), timestamp DATE
C Chang Received on Wed Mar 05 2003 - 23:17:05 CST