9-15 企业数据更新

update company_test ,no_ic_code  set company_test.ic_code = no_ic_code.执照注册号
where  no_ic_code.企业名称 = company_test.name


update company_test ,t_all  set company_test.ic_code = t_all.工商登记号
where t_all.企业名称 = company_test.name
and (t_all.`工商登记号` REGEXP '^[[:blank:]]{0,3}([A-Z]|[[:digit:]]){15}[[:blank:]]{0,3}$' =1
or t_all.`工商登记号` REGEXP '^[[:blank:]]{0,3}([A-Z]|[[:digit:]]){13}[[:blank:]]{0,3}$'=1
or t_all.`工商登记号` REGEXP '^[[:blank:]]{0,3}([A-Z]|[[:digit:]]){13}[[:blank:]]{0,3}$'=1
or  t_all.`工商登记号` REGEXP '^[[:blank:]]{0,3}[BCF][[:digit:]]{5}[[:blank:]]{0,3}$'=1
 or t_all.`工商登记号` REGEXP '^[[:blank:]]{0,3}([[:alpha:]]+第[[:digit:]]+号?)[[:blank:]]{0,3}$' =1
)


update company_test ,ic_ischinese
set company_test.ic_code = ic_ischinese.`执照注册号`
 where company_test.name = ic_ischinese.`企业名称`



create table  irregular_org_code_new as  SELECT *  FROM irregular_org_code

update company_test,irregular_org_code_new set company_test.org_code = replace(replace(备注栏,"朝阳  ",""),"顺义  ","")

where irregular_org_code_new.企业名称 = company_test.name and  replace(replace(备注栏,"朝阳  ",""),"顺义  ","") regexp
'^[[:blank:]]{0,3}([A-Z]|[[:digit:]]){9}[[:blank:]]{0,3}$'

update company_test ,irregular_org_code_new set company_test.org_code_syned = '1'
 where company_test.name = irregular_org_code_new.企业名称
and irregular_org_code_new.备注栏 = '√'
-------------------------------------------------------------------

update no_reg_in_guo_di2 set 是否异地纳税 = '1'
where no_reg_in_guo_di2.是否异地纳税 like '%是%'  or
no_reg_in_guo_di2.是否异地纳税 = '异地纳税' ;

update no_reg_in_guo_di2 set 是否异地纳税 = '-1'
where no_reg_in_guo_di2.是否异地纳税 = '否' ;

update company_test, no_reg_in_guo_di2 set company_test.state_tax_code = no_reg_in_guo_di2.国税税务登记号,
company_test.local_tax_code = no_reg_in_guo_di2.地税税务登记号,
company_test.ydns =  no_reg_in_guo_di2.是否异地纳税
where company_test.name = no_reg_in_guo_di2.企业名称

------------------------------------------------------------------------------


update no_reg_in_guo_di , company_test set company_test.state_tax_code = no_reg_in_guo_di.国税登记号
WHERE company_test.name = no_reg_in_guo_di.企业名称

update company_test ,t_all  set company_test.ic_code = replace(t_all.工商登记号,"迁出","")
where t_all.企业名称 = company_test.name
and (

t_all.`工商登记号` REGEXP '^[[:blank:]]{0,3}迁出([A-Z]|[[:digit:]]){15}[[:blank:]]{0,3}$' =1
or t_all.`工商登记号` REGEXP '^[[:blank:]]{0,3}迁出([A-Z]|[[:digit:]]){13}[[:blank:]]{0,3}$'=1
or t_all.`工商登记号` REGEXP '^[[:blank:]]{0,3}迁出([A-Z]|[[:digit:]]){13}[[:blank:]]{0,3}$'=1
or t_all.`工商登记号` REGEXP '^[[:blank:]]{0,3}迁出[BCF][[:digit:]]{5}[[:blank:]]{0,3}$'=1
or t_all.`工商登记号` REGEXP '^[[:blank:]]{0,3}迁出([[:alpha:]]+第[[:digit:]]+号?)[[:blank:]]{0,3}$' =1
 

)
commit;

select * from company_test where name ='东美国际航空旅行社有限公司'
775455158

update company_test set ic_code = '775455158' where name ='东美国际航空旅行社有限公司'

select * from company_test where name like '%北京北人羽新有限公司（办公室）%'
801236427

update company_test set ic_code = '801236427' WHERE name like '%北京北人羽新有限公司（办公室）%'