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：
| A | |
| 1 | =myDB1.query(“select ROWID from TMP_SURVEY_TRAN_BZ_3_WORKING where Suppression_Type_Def_Id =999 and customer_type=’r’ and BankNum=? and Call_BranchNumber=?” ,lv_Bank_Num,lv_Branch_Num) |
| 2 | =myDB1.query(“select ROWID from TMP_SURVEY_TRAN_BZ_3_WORKING where Suppression_Type_Def_Id =999 and customer_type!=’r’ and BankNum=? and Call_BranchNumber=?” ,lv_Bank_Num,lv_Branch_Num) |
| 3 | =if(A1.count()>=20,A1.sort(rand()).to(20),A2.sort(rand()).to((20-A1.count()))|A1) |
| 4 | =myDB1.update@u(A3,TMP_SURVEY_TRAN_BZ_3_WORKING,ROWID,Quota_Include_Ind:”Y”;ROWID) |
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.