| keeping distinct rows [message #52436] |
Mon, 22 July 2002 03:13  |
santosh
Messages: 85
Registered: October 2000
|
Member |
|
|
Hello Friends
Please help me in executing little excercise , I got table with more than 5 columns which have many records but have some reentered duplicate records (not duplicate few fields but entire records duplicate) just when u do insert into table a(select * from table a) twice or thrice.
Now can anybody help me in writing single sql statements which deletes all duplicate records keeping single record for such records. I mean after i execute the query duplicate records should become distinct records( that means repeating records in 2s or 3s or 4s must become only 1s)
I tried this using rowid funda but it was not completely satisfing for multiple columns table .
|
|
|
|
| Re: keeping distinct rows [message #52437 is a reply to message #52436] |
Mon, 22 July 2002 03:53  |
Saga
Messages: 51
Registered: April 2002
|
Member |
|
|
Well its wise if u divide the entire scenario in 3 steps.
Step 1. Create a table a similiar to ur table. Populate the table with distinct records from the original table.
Step 2. Truncate the Original table.
Step 3. Populate the Original table with the records from the table u have create.
So sequence of steps that will be executed is
SQL> CREATE TABLE t2 AS (SELECT DISTINCT a.* FROM t1 a)
SQL> TRUNCATE TABLE t1;
SQL> INSERT INTO t1 (SELECT * FROM t2 );
Hope it helps.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
