Oracle script need to remove specific duplicate entries from a table.
Hello expert,
This is bit urgent.
I have table claims_A with about 10 columns.
There about 2.6 million records in the table.
I have just inserted about 5000 records in the table and just realized there were 18 duplicate records in my insert statement.
so now I have 18 duplicates records ClaimsA
What I urgently need to do is just delete these specific 18 duplicate records that I added.
Is there a fast script that can do it?
Mind you there could be many other duplicate records (other than what I added) but I only want to remove the duplicate that I added.
Is there quick way to do this?
Thank you.
al
This is bit urgent.
I have table claims_A with about 10 columns.
There about 2.6 million records in the table.
I have just inserted about 5000 records in the table and just realized there were 18 duplicate records in my insert statement.
so now I have 18 duplicates records ClaimsA
What I urgently need to do is just delete these specific 18 duplicate records that I added.
Is there a fast script that can do it?
Mind you there could be many other duplicate records (other than what I added) but I only want to remove the duplicate that I added.
Is there quick way to do this?
Thank you.
al
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Whether either this "select..." query or the "delete..." statement will be fast or not, depends on whether your "key_column(s)" is/are indexed, or not. It/they certainly should be indexed! But, apparently there is no unique index on it/them now.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Mark.
Your suggested script worked but was taking time, so I had to include slightwv's suggestion
Thank you for your prompt response
Appreicated.
Your suggested script worked but was taking time, so I had to include slightwv's suggestion
Thank you for your prompt response
Appreicated.
"...worked but was taking time". That indicates a lack of an index on the key column(s) in your table. That is usually not a good plan. Also, the fact that the duplicates were allowed to be inserted, clearly indicates the lack of a unique index. That is also usually not a good idea with Oracle tables.
To prevent this problem from happening in the future, you should create a unique index on this table. Is there something that prevents you from creating an index like this?
To prevent this problem from happening in the future, you should create a unique index on this table. Is there something that prevents you from creating an index like this?
I believe the main reason Mark's method took a long time is because it was going after ALL duplicates in the table not just the 18 you wanted. Yes, lack of indexes is the root cause but it also wouldn't have given you what you wanted even if indexes existed.
ASKER
Mark/slightwv
Agree there is problem with the way the table was designed. Since I am not the owner of the table, I will pass your suggestion about putting index and not allow duplicates that will make things easier in future. Thank you so much for your suggestion which solved the issue at hand,
will your help in future too!
Cheers
Al
Agree there is problem with the way the table was designed. Since I am not the owner of the table, I will pass your suggestion about putting index and not allow duplicates that will make things easier in future. Thank you so much for your suggestion which solved the issue at hand,
will your help in future too!
Cheers
Al
Can you give us the actual table and column names? Or, do you want us to just give you a generic SQL query that you will have to modify with your actual table and column names?
To prevent duplicates from being entered into this table in the future, you could (I say should) create a primary or unique key index that includes the key column(s).