How to change a key field for all records in a table without causing an infinite loop

How to avoid an infinite loop when a key field value is changed for all records in a table?
Updating indexed fields within a FOR EACH causes an infinite loop

When using a FOR EACH statement where the field being modified is part of the index that was selected by the compiler, an infinite loop can happen because the FOR EACH goes back to the indexes in the database to find the next record to process and if the change to the key field moved the record further down in the index then the record will be processed again:

 

FOR EACH Customer EXCLUSIVE-LOCK:
    ASSIGN CustNum = CustNum + 5000.  
   /* This record will now be further down in the index and will be picked up again, causing an infinite loop */
END.

To avoid an infinite loop when updating indexed fields within a FOR EACH
 

1. Use another index in the FOR EACH statement

The USE-INDEX phrase to specify another index which does not contain the key field(s) that are being updated.  

Selecting an inappropriate index will result in a less efficient and thus slower query.

 

FOR EACH Customer USE-INDEX Name EXCLUSIVE-LOCK:
    ASSIGN CustNum = CustNum + 5000.  
/* CustNum is not part of the Name index, which results in no looping on that index */
END.

2. Use a PRESELECT loop instead of the FOR EACH statement

PRESELECT will build a fixed result list up front and then process the records in that list only once.
 

DEFINE QUERY NoInfiniteLoopsWanted FOR Customer.

OPEN QUERY NoInfiniteLoopsWanted PRESELECT EACH Customer.

GET FIRST NoInfiniteLoopsWanted.

REPEAT WHILE QUERY-OFF-END("NoInfiniteLoopsWanted") = FALSE:
    ASSIGN CustNum = CustNum + 5000.
    GET NEXT NoInfiniteLoopsWanted.
END.

CLOSE QUERY NoInfiniteLoopsWanted.

Use a REPEAT PRESELECT loop:

REPEAT PRESELECT Customer EXCLUSIVE-LOCK:
   FIND NEXT Customer.
   ASSIGN CustNum = CustNum + 5000.
END.

 

Progress Articles:

 What is the cost of adding a new index?    
 What is the benefit of using the TABLE-SCAN option?