Db2/cursor Program Working In Cobol

My requirement is to delete data from db2 table which contains duplicate phone num(invalid phone num, set of 16 phone numbers which is hardcoded).And one phone num should be retain

Solution 1:

You can delete them all in one go with the following SQL

DELETEFROM PHONE_TAB A
 WHEREEXISTS (SELECT1FROM PHONE_TAB B
                WHERE A.PHONE_NUM = B.PHONE_NUM AND
                      A.SEQ_NUM > B.SEQ_NUM)

It works by deleting all rows where another row exists that has the same phone number and a lower sequence number.

You can check that it deletes the right rows by first running it with a select, like this

SELECT*FROM PHONE_TAB A
 WHEREEXISTS (SELECT1FROM PHONE_TAB B
                WHERE A.PHONE_NUM = B.PHONE_NUM AND
                      A.SEQ_NUM > B.SEQ_NUM)

If you need to backup the rows, you can run the select and then delete the rows.