Re: Need to delete duplicate rows

create table DUPLICATE_TBL as select distinct * from TBL;

Well, not quite. the phrase "distinct *" will give you an error unless it's been permitted in some version of Oracle that I haven't used. however, if you replace the "*" with a list of all the columns in the table, in the right order, then the duplicate will get one representative row for pair of identical rows.

This implies a sort, so be prepared for a long execution time.

After you get the duplicate table, it's fairly routine to get the distinct rows moved back into the original table.

• wrote in message <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! =-----