Arati Vijay <vijayj00_at_singnet.com.sg> wrote in article
<7vu9v8$a3d$1_at_coco.singnet.com.sg>...
: TRY THIS
:
: CREATE TABLE NO_DUPE AS (SELECT DISTINCT * FROM DUPE_TABLE)
: DROP TABLE DUPE_TABLE
: ALTER TABLE NO_DUPE RENAME TO DUPE_TABLE
:
: Incidentally, this is a question that I also ask at interviews, and
ain't
: got an answer to date.
The solution using DDL statements is, obviously, the
worst one. There is a well-known solution, which, as it
appeared, comes to mind to most Oracle freshmen worth of
employing, though it takes them some time.
People with some experience usually know it.
DELETE DUPE_TABLE D1 WHERE EXISTS
(SELECT 1 FROM DUPE_TABLE WHERE AA=D1.AA AND BB=D1.BB
AND ROWID>D1.ROWID);
Of course, if the DUPE_TABLE is huge and not indexed properly, the
above query works eternally, so it is worth
to create a non-unique index on (AA, BB) in this case.
--
Alexander I.Doroshko, aid_at_grant.kharkov.ua
- Bank "Grant", Kharkov, Ukraine
:
: <argosy22_at_my-deja.com> wrote in message
news:7vss7n$c36$1_at_nnrp1.deja.com...
: > You have a table, DUPE_TABLE,
: > with two fields, AA, and BB.
: > No primary key. Dupe data.
: > Only 6 rows.
: > 2 sets of the same exact 3 rows.
: >
: > DUPE_TABLE:
: > AA BB
: > 1 x
: > 2 y
: > 3 z
: > 1 x
: > 2 y
: > 3 z
: >
: >
: > You want to delete 3 rows, so that
: > you have only one set of 3:
: >
: > AA BB
: > 1 x
: > 2 y
: > 3 z
Received on Sat Nov 06 1999 - 03:32:53 CST
