• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 661
  • Last Modified:

removal of duplicate records in a table in an informix 6 database

i am using an informix 6 database.
I have a tabl;e which unfortunately has been updated twice with the same records.
How can I remove the duplicate set of record uing sql, the records are identical - I thought that perhaps use of the rowid may be the answer - can you help
0
bigelz1215
Asked:
bigelz1215
  • 2
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:

Hi bigelz1215,

This is a DB2 forum, so be wary of answers to non-DB2 questions.  :)

The solution below won't work on DB2, but it will work on other flavors of SQL.  Your mileage may vary....

delete table_a
where rowid not in
     (select min(rowid) from table_a
       group by column1, column2);


You can also chase a cursor through the data, selecting the duplicates via the cursor and deleting them by the cursor.


Good Luck!
Kent
0
 
ghp7000Commented:
I think you must have misused the word 'updated' with inserted. I assume what you mean is that the same set of records was inserted more than once.
Yes, row_number() function in DB2 will do what you want, unfortunately I have no idea if this is the same functionality as ROWID in Informix.
Another way is to create a new table that is identical to the table with the unwanted duplicate records. Then insert into this table only one copy of the duplicate records:
INSERT INTO NEWTABLE
SELECT A, B, C
FROM OLDTABLE
GROUP BY A,B,C
HAVING COUNT(*)>1
Next, delete ALL THE DUPLICATE RECORDS from the original table
DELETE FROM OLDTABLE
WHERE EXISTS (SELECT * FROM NEWTABLE WHERE OLDTABLE.A=A AND ...)
Next,  "copy" all record from new to old table
INSERT INTO ORIGINAL_TABLE (SELECT * FROM NEW_TABLE)
Finally, drop the new table

Or, chase the cursor as Kent suggests.
0
 
Kent OlsenData Warehouse Architect / DBACommented:

And I hate chasing cursors.   :(
0
 
bigelz1215Author Commented:
Thanks for the help - I have used your idea with rowids and have come up with a solution which has worked
many thanx
dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now