home *** CD-ROM | disk | FTP | other *** search
- Path: sparky!uunet!lhdsy1!usho93!usho32.hou281.chevron.com!agmkne
- From: agmkne@usho32.hou281.chevron.com (M.K.Netzband)
- Newsgroups: comp.databases.oracle
- Subject: Re: Deleting Duplicates
- Message-ID: <1992Jul23.180919.24200@usho93.hou281.chevron.com>
- Date: 23 Jul 92 18:09:19 GMT
- References: <VN70NB1w165w@iowegia.uucp> <1992Jul23.091611.1@jaguar.uofs.edu>
- Sender: news@usho93.hou281.chevron.com (USENET News System)
- Organization: Chevron
- Lines: 23
-
- In article <1992Jul23.091611.1@jaguar.uofs.edu> littlec1@jaguar.uofs.edu (Chris Little) writes:
- >In article <VN70NB1w165w@iowegia.uucp>, bob@iowegia.uucp (Bob Powers) writes:
- >> How can I delete duplicate rows in a table using SQL*PLUS. This is
- >> an unindexed table and I would like to keep it that way.
- >
- >I'm relatively new at the job, but I suspect you could create a similar
- >table, do an INSERT SELECT UNIQUE from the old table to the new table,
- >delete all rows from the old table, copy all rows from the new table
- >to the old, and then drop the new one. Maybe there's a better way.
- >
-
- Try customizing the following...
-
- delete from TABLE_NAME a
- where a.rowid < (select max(b.rowid)
- from TABLE_NAME b
- where b.field1 = a.field1
- and b.field2 = a.field2);
-
-
-
- Mike
-
-