Adding Sequence Numbers
To add a new sequence number based on existing key structures where the sequence number increments based on those keys the following query logic applies. This would be used when there is data already present and PeopleCode will not be triggered to set the sequence number.
How this query works is to group results based on keys and then find the count of rows with a rowid greater than or less than its rowid.
UPDATE RECORD_NAME T1 SET T1.SEQNUM_FIELD= ( SELECT Count(*) FROM RECORD_NAME T2 WHERE T1.KEY_FIELD_1 = T2.KEY_FIELD_1 AND T1.KEY_FIELD_2 =T2. KEY_FIELD_2 AND T2.ROWID >= T1.ROWID GROUP BY T2.KEY_FIELD_1, T2.KEY_FIELD_2 )