Re: Delete the duplicated ones

C Chang <cschang_at_maxinter.net> wrote in message news:<3E66D9D1.12AD_at_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

C, Have you considered the possiblity that some of the duplicated keys might have multiple duplicates. If you are counting by distinct key value combinations and then comparing this to the delete results they will differ in number of rows. You might want to take a look at your count of duplicates to make sure it performs a sum of count by keys to compare against.

HTH -- Mark D Powell -- Received on Thu Mar 06 2003 - 12:03:28 CST