0

I have a question. We have a huge table where unfortunately duplicates have been loaded. We had dropped the Indexes and Constraints for loading History data and Team have inadvertently reloaded already loaded data on failure without doing proper rollback.

This has caused exact duplicates on the table and I have to delete only one of them I scoured the Internet but couldn't find anything for DB2 ZOS.

I found the below one but I think this for LUW and doesn't work on ZOS.

DELETE FROM (SELECT ROW_NUMBER() OVER (PARTITION BY COL1) AS RN FROM ABC.TABLE) AS TMP WHERE RN > 1;

Any help would be greatly appreciated.

Thanks, Kalai

Kalaiselvan Shanmugam
  • 1
  • One easy way is 1. SELECT the DISTINCT records into a New Table 2. TRUNCATE the Old Table 3. MERGE the New Table back into the Old Table – Nimesh Gami Dec 21 '17 at 10:40
  • Nimesh, The table is too big and we don't want to go that way. – Kalaiselvan Shanmugam Dec 21 '17 at 10:58
  • Use this one might be it will work : WITH CTE(COL1,COL2,COL3) AS ( SELECT ROW_NUMBER() OVER (PARTITION BY COL1 order by COL1,COL2,COL3) AS RN FROM ABC.TABLE ) DELETE FROM CTE WHERE RN > 1 – Nimesh Gami Dec 21 '17 at 11:04

0 Answers0