support
buy cialis buy valium online buy viagra online

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_sql_random_update_1

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:

esProc_sql_random_update_3

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

replica franck-muller watches  replica hublot watches  replica panerai watches  replica bvlgari watches  replica patek-philippe watches  replica montblanc watches  replica longines watches  replica hermes watches  replica audemars piguet watches  replica vacheron constantin watches