esProc Performs Random UPDATE with Priorities

Problem Source:

https://www.linkedin.com/groups/update-records-based-on-rank-1890173.S.5914444354742661123?trk=groups_items_see_more-0-b-ttl .

Target：Among the records that satisfy the specified condition in the TMP_SURVEY_TRAN_BZ_3_WORKING table, select 20 ones at random and change values of their Quota_Include_Ind field into “Y”. But there is a list of priorities for the update: If the number of records satisfying customer_type='r' is greater than 20, then the 20 random records for updating will be chosen from them; if the number of records satisfying the same condition is less than 20 (say 15), then we’ll update all these 15 records, plus another 5 ones chosen randomly from records that satisfy customer_type<>'r'.

Below is a selection from the original data：

esProc approach：

A1,A2：Both execute SQL statement to retrieve primary key values of the eligible records according to the parameters. A1’s records meet condition customer_type='r' and A2’s record meet condition customer_type<>'r'.

A3: Get primary key values of the records to be updated according to the number of records in A1. A1.sort(rand()) means sorting A1 randomly; to(20) equals [1,2…20], which means getting 20 records in order. The operator | is used to concatenate two sets.

Results of A1, A2 and A3 are listed separately as follows:

A4: Update the table based on A3. @u indicates that only UPDATE statement will be generated.