<div>
<br />
Hi bigelz1215,<br />
<br />
This is a DB2 forum, so be wary of answers to non-DB2 questions. &nbsp;:)<br />
<br />
The solution below won't work on DB2, but it will work on other flavors of SQL. &nbsp;Your mileage may vary....<br />
<br />
delete table_a <br />
where rowid not in <br />
&nbsp; &nbsp; &nbsp;(select min(rowid) from table_a <br />
&nbsp; &nbsp; &nbsp; &nbsp;group by column1, column2);<br />
<br />
<br />
You can also chase a cursor through the data, selecting the duplicates via the cursor and deleting them by the cursor.<br />
<br />
<br />
Good Luck!<br />
Kent
</div>



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

<div>
<br />
And I hate chasing cursors. &nbsp; :(
</div>


<div>
Thanks for the help - I have used your idea with rowids and have come up with a solution which has worked<br />
many thanx<br />
dave<br />

</div>


Thanks for the help - I have used your idea with rowids and have come up with a solution which has worked
many thanx
dave


<div>
<div class="content wysiwyg-content">
i am using an informix 6 database.<br />
I have a tabl;e which unfortunately has been updated twice with the same records.<br />
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
</div>
</div>


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

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

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.