Re: Need to delete duplicate rows

Someone suggested select distinct into a temp table which will work, another solution would be:

(Its been a while since I have had to do this so you may have to play with the syntax)

delete from sales a
where rowid = (select max(b.rowid)

    where a.item_code = b.item_code
    and <snip>
    );

Naturally run the equivilent select count(*) first to check how many you will delete. :)

HTH
Karl

• <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
  >
  > I would be grateful if anyone could please tell me how I could use SQL to
  > remove the duplicate rows from my table.
  >
  > Many thanks.
  >
  >
  >
  >
  > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
  > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
  > -----== Over 80,000 Newsgroups - 16 Different Servers! =-----