update panelNo SQL

巧妙的更新panelNo的SQL

--495

update cell2_chip_t set item30= trim(to_char( trunc((chip_no+1)/2) * 4-1 - chip_no,'00')) ,item29= trim(cf_glass_id) || trim(to_char( trunc((chip_no+1)/2) * 4-1 - chip_no,'00')) where 1=1 and record_index >= 201712010000000000 and  record_index < 201712020000000000 and product_id like'%495%' and (substr(item29,1,1) <>'C' or item29  is null);commit;

--315

update cell2_chip_t set item30= trim(to_char( 6*(trunc((chip_no+2)/3)-1)+4 - chip_no,'00')),item29=  trim(cf_glass_id) || trim(to_char( 6*(trunc((chip_no+2)/3)-1)+4 - chip_no,'00')) where 1=1 and record_index >= 201808010000000000 and  record_index < 201808020000000000 and product_id like'%315%' and (substr(item29,1,1) <>'C' or item29  is null);commit;

--575

update cell2_chip_t set item30= trim(to_char( 6*(trunc((chip_no+2)/3)-1)+4 - chip_no,'00')),item29=  trim(cf_glass_id) || trim(to_char( 6*(trunc((chip_no+2)/3)-1)+4 - chip_no,'00')) where 1=1 and record_index >= 201802010000000000 and  record_index < 201802020000000000 and (product_id like'%575%' or product_id like'%315%') and (substr(item29,1,1) <>'C' or item29  is null);commit;

--695

--not panel 5

update cell2_chip_t set item30= trim(to_char( 6*(trunc((chip_no+2)/3)-1)+4 - chip_no,'00')),item29=  trim(cf_glass_id) || trim(to_char( 6*(trunc((chip_no+2)/3)-1)+4 - chip_no,'00')) where 1=1 and record_index >= 201807010000000000 and  record_index < 201807020000000000 and (product_id like'%695%') and chip_no<'05' and (substr(item29,1,1) <>'C' or item29  is null);commit;

-- is Panel 5

update cell2_chip_t set item30= '05',item29=  trim(cf_glass_id) || '05' where 1=1 and record_index >= 201807010000000000 and  record_index < 201809080000000000 and (product_id like'%695%') and AND chip_no = '05' and (substr(item29,1,1) <>'C' or item29  is null);commit;