Duplicate records [message #9226] |
Tue, 28 October 2003 02:14 |
Cindreen Clarence
Messages: 41 Registered: March 2003 Location: Bangalore
|
Member |
|
|
Hi,
I have duplicate rows in my table, coz no primary keys were defined. Can anybody plz tell me how to remove the duplicate records in such a way that one of the record stays in the database?
Thanks
|
|
|
|
Re: Duplicate records [message #9230 is a reply to message #9226] |
Tue, 28 October 2003 02:59 |
Ravi M
Messages: 2 Registered: October 2003
|
Junior Member |
|
|
You can use the following query 2 remove duplicates, if u dont have the primary key.
DELETE FROM DUPLICATES A WHERE ROWID > (
SELECT MIN(ROWID) FROM DUPLICATES B
WHERE A.col1 = B.col1 AND A.col1 = b.col1 AND a.col1 = b.col1 )
|
|
|
Re: Duplicate records [message #9231 is a reply to message #9226] |
Tue, 28 October 2003 03:00 |
Ravi M
Messages: 2 Registered: October 2003
|
Junior Member |
|
|
You can use the following query 2 remove duplicates, if u dont have the primary key.
DELETE FROM DUPLICATES A WHERE ROWID > (
SELECT MIN(ROWID) FROM DUPLICATES B
WHERE A.col1 = B.col1 AND A.col2 = b.col2 AND a.col3 = b.col3 )
|
|
|