Re: Need to delete duplicate rows

From: Thomas Muller <ttm_at_nextra.com>
Date: 2000/04/17
Message-ID: <xzFK4.4699$By1.93671_at_news1.online.no>#1/1

• <NICKB_at_KUBOTA.CO.UK> wrote in message news:38fae4cb.0_at_tahiti.alcom.co.uk...
  > Hi folks.
  >
  > Our Oracle database receives data from AS/400 on a daily basis.
  > Unfortunately, for whatever reason, Oracle has imported (from AS/400) into
  > my 'SALES' table a duplicate row for each record that exists. For e.g.:
  >
  > item_code: invoice_date: serial_no: engine_no: sales_qty:
  > x234-88 03-APR-00 45677 DE4344 1
  > x234-88 03-APR-00 45677 DE4344 1
  >
  

This would be avoided if the table was defined with a primary key. The primary key constraint prevents duplicate tuples with respect to the defined key.

> I would be grateful if anyone could please tell me how I could use SQL to
> remove the duplicate rows from my table.
>

Allowing duplicate rows violates 1NF, hence the table is not valid according to relational theory. Since you can't specify which row to delete (it's not identifiable) you can only remove _all rows_ being duplicates of eachother with standard SQL. You can, however, create a procedure (Java or PL/SQL) that removes the duplicate rows, but the best solution is still to not allow them in the first place.

Assume item_code to be your "virtual" primary key.

PROCEDURE remove_duplicates
IS

    CURSOR item_cur IS

        SELECT item_code FROM sales ORDER BY item_code;     item_rec item_cur%ROWTYPE;
    last_item VARCHAR2;
BEGIN
    last_item = '';
    FOR item_rec IN item_cur
    LOOP

        IF (item_rec.item_code = last_item)
            DELETE FROM sales WHERE CURRENT OF item_cur;
        END IF;
        last_item := item_rec.item_code;

    END LOOP;
END; Note that this is not compiled nor tested.

--

Thomas


create

Received on Mon Apr 17 2000 - 00:00:00 CEST