Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Delete the duplicated ones

Re: Delete the duplicated ones

From: C Chang <cschang_at_maxinter.net>
Date: Thu, 06 Mar 2003 00:17:05 -0500
Message-ID: <3E66D9D1.12AD@maxinter.net>


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),

  unit_price NUMERIC(14,5),
  price_status VARCHAR2(1),
  req_delivery_date DATE,
  priority      VARCHAR2(2),
  open_flag     VARCHAR2(2),
  pol_remark    VARCHAR2(240),
  order_id      VARCHAR2(12),
  edd_date      DATE,

  pol_status VARCHAR2(2),
  shipping_status VARCHAR2(3),
  advice_code VARCHAR2(2),
  audit_info audit_T
)
/
where audit_t is

CREATE OR REPLACE TYPE audit_T AS OBJECT (

  username      VARCHAR2(20), 
  auditmode     VARCHAR2(3),
  timestamp     DATE   

)
/
without including the possible NULL value column, I will return different number of rows deleted. I used the reference to compare the Audit_t, ex table.audit_info.username. By the way, my method was from the google.com and askTom.oracle.com. Thanks.

C Chang Received on Wed Mar 05 2003 - 23:17:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US