-
Hi friends,
I want to know how to delete duplicate records in a table. My table has 3 fields: col1, col2 and col3. I want to delete duplicate records which have same values in col1 and col2.
Thanks
manjunath
-
Try this:
delete from your_table a where rowid not in
(select min(rowid) from your_table b where
a.col1=b.col1 and a.col2=b.col2);
Regards,
-
Hi
Try this
1) To display duplicate records using the following query
Select col1, col2, count(*) duplicates
from tablename
group by col1, col2
having count(*) > 1;
2) To delete duplicate record use the following query
DELETE from tablename a
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM tablename b
where a.col1 = b.col1
and a.col2 = b.col2);
Regards
Santosh
-
thanks a lot, Santosh and Dmitriy
manjunath
-
check this out please...
Delete from TableA a where
A.p_key > (Select min(b.p_key) from TableA b
Where a.p_key = b.p_key) ;
which will preserve the last inserted data & remove the older ones
Delete from TableA a where
A.p_key < (Select max(b.p_key) from TableA b
Where a.p_key = b.p_key) ;
will preserve the first entries and remove the later additions.
A Winner never Quits, A Quitter never Wins
Ganesh .R
-
Originally posted by santoshym
Hi
Try this
1) To display duplicate records using the following query
Select col1, col2, count(*) duplicates
from tablename
group by col1, col2
having count(*) > 1;
2) To delete duplicate record use the following query
DELETE from tablename a
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM tablename b
where a.col1 = b.col1
and a.col2 = b.col2);
Regards
Hi,
I had the same issue today where i needed to delete duplicate records and i searched the forum and came across this post.
I used the able query and it worked great.
I just wanted to know what steps does the second statement above go through to achieve the objective,
DELETE from tablename a
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM tablename b
where a.col1 = b.col1
and a.col2 = b.col2);
Any response would be really appreciated.
Thanks
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
If two or more rows have similar data it deletes all the rows but one with the lowest rowid.
-
Originally posted by irehman
If two or more rows have similar data it deletes all the rows but one with the lowest rowid.
I know but I am trying to figure out what exactly the query does to delete the duplicate records.
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
Originally posted by irehman
If two or more rows have similar data it deletes all the rows but one with the lowest rowid.
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|