473,326 Members | 2,175 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Duplicate Records

Stang02GT
1,208 Expert 1GB
Somehow i got a duplicate recorded into one of my tables.

Is there any way I can edit them, or will I have to delete both of the records and re-insert the correct records?
May 9 '08 #1
3 2197
debasisdas
8,127 Expert 4TB
try to use this sample code for reference
Expand|Select|Wrap|Line Numbers
  1. Delete from emp e 
  2. where rowid>(select min(rowid) from emp 
  3. where e.empno=empno)
May 12 '08 #2
amitpatel66
2,367 Expert 2GB
you can try this as well:

Expand|Select|Wrap|Line Numbers
  1.  
  2. delete from table_name where ROWID NOT IN (SELECT MAX(rowID) FROM table_name group by empno)
  3.  
  4.  
This will delete older records
May 13 '08 #3
r035198x
13,262 8TB
Somehow i got a duplicate recorded into one of my tables.

Is there any way I can edit them, or will I have to delete both of the records and re-insert the correct records?
Elaborating a bit on the replies given so far. The important things to note when trying to get rid of duplicate records are :


  • Back up first. Deleting records can cause several unprintable sounds to be uttered by the user/DBA who has just executed it.
  • Define duplicate. A duplicate is only identifiable if the uniqueness attributes have been well defined. This is important especially where multiple columns constitute the uniqueness attributes set.
  • A delete statement which also works for multiple uniqueness columns is :
Expand|Select|Wrap|Line Numbers
  1. DELETE FROM table_name
  2. WHERE rowid NOT IN
  3. (SELECT MIN(rowid)
  4. FROM table_name
  5. GROUP BY col1, col2, col3... ;
  6.  
Here col1, col2, e.t.c are the uniqueness columns.rowid is a special built in oracle "column" (pseudo-column) which uniquely identifies each row in a table. So every Oracle row has got it.
Note that rowid should not be used as part of the data of a row because even though it is unique, it changes every time you do EXP,IMP or in indexed and partitioned tables.


The inner select retrieves all the duplicates (as defined by the uniqueness attributes col1,col2, e.tc).
The delete then deletes all of them except the one with the least rowid.
  • Usually your table design should be structured in such a way that duplicates should not be possible. However, sometimes ghosts fly in the DB e.t.c. It's important though to recheck your table design everytime you see unintended duplicates.
Jun 3 '08 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Gary Lundquest | last post by:
It appears to me that MySQL version 4 returns an error messge when doing an Insert that results in duplicate entries. Version 3 did NOT return an error - it dropped the duplicate entries and ran...
2
by: ms | last post by:
Access 2000: I am trying to delete duplicate records imported to a staging table leaving one of the duplicates to be imported into the live table. A unique record is based on a composite key of 3...
4
by: KT | last post by:
Is there any one click solution that would do the trick? I would like to create a button, so the person who maintains the database can perform clean up work to delete duplicate records which...
2
by: Carroll | last post by:
I'm looking for a way in SQL to find duplicate records in a single table, that are the same based on 3 columns, regardless of what is in the other columns in the duplicate records. I would like to...
0
by: B.N.Prabhu | last post by:
Hi, I have a DataTable with several rows. Its having 20 Columns. when i click the Insert button then i have to check the Database Rows. Whether these new rows are already available in the...
2
by: nethravathy | last post by:
Hi, The following table namely elcbtripselect contains 5147 records.I want to know wether this table contains duplicate records or not. I tried with following query 1)SELECT...
4
by: Thomas Arthur Seidel | last post by:
Hello to all, I have a small or big problem with a customer data base, where during a change of system we might have created duplicate records. This should be easy to find, you might think, but,...
2
by: nomvula | last post by:
hi guys i need some help to duplicate records on my form datasheet: here's the example of my form results: ClientLookup DateCaptured ForecastDate Description ForecastQuantity Forecast Actual UJ...
6
by: Dilip1983 | last post by:
Hi All, I want to delete duplicate records from a large table. There is one index(INDEX_U1) on 4 columns(col1,col2,col3,col4) which is in unusable state. First of all when i tried to rebuild...
1
by: xraive | last post by:
I have a problem with this. Currently I am trying Allen's code and i am not successful. Current Design Table1 (Main Form) TravelID (PK) ApprovedBY EntreredBy BudgetCode ExpenseCode
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.