[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 578
  • 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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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