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：
|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)|
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.