INSERT INTO public.tenant_users(id, authenticated_status, authenticated_result, authenticated_time, birthdate, checked_status, created_time, email, habit_setting, head_photo, im_identity, lon_lat_json, name, sex, stature, telephone, user_type, selected_credential_id, household_type, one_inch_color_white_photo, two_inch_color_blue_photo, education, marital_status, military_status, nation, native_place, politics_status) select tu.id, (case when tu.FIELD21='已认证' then 3 when tu.FIELD23='未认证' then 0 else 2 end), tu.FIELD23, tu.LASTMODIFYTIME, null,(case when tu.FIELD13 ='已核验' then 1 when tu.FIELD13='未提交' then 0 else 2 end), COALESCE(tu.LASTMODIFYTIME,CURRENT_TIMESTAMP),tu.email,null as habit_setting, tu.FIELD20 as head_photo, null as im_identity, tu.FIELD25 as lon_lat_json,tu.NAME,0, null as stature, tu.LOGINNO as telephone, 0 as userType, null as selected_credential_id,null as household_type, null as one_inch_color_white_photo,null as two_inch_color_blue_photo, null as education,null as marital_status,null as military_status, null as nation, null as native_place,null as politics_status from t_user tu join identity_user iu on tu.ID=iu.id where iu.id not in (select id from tenant_users) and iu.telephone not in (select telephone from tenant_users); /** 手动删除一些测试的账号信息，比如: 测试环境的如下信息是我们再验证的时候产生的tenant_user select * from tenant_users where telephone in ('15959298383','15959298385','15959394811'); 处理之后，再执行一遍 */ INSERT INTO public.tenant_users(id, authenticated_status, authenticated_result, authenticated_time, birthdate, checked_status, created_time, email, habit_setting, head_photo, im_identity, lon_lat_json, name, sex, stature, telephone, user_type, selected_credential_id, household_type, one_inch_color_white_photo, two_inch_color_blue_photo, education, marital_status, military_status, nation, native_place, politics_status) select tu.id, (case when tu.FIELD21='已认证' then 3 when tu.FIELD23='未认证' then 0 else 2 end), tu.FIELD23, tu.LASTMODIFYTIME, null,(case when tu.FIELD13 ='已核验' then 1 when tu.FIELD13='未提交' then 0 else 2 end), COALESCE(tu.LASTMODIFYTIME,CURRENT_TIMESTAMP),tu.email,null as habit_setting, tu.FIELD20 as head_photo, null as im_identity, tu.FIELD25 as lon_lat_json,tu.NAME,0, null as stature, tu.LOGINNO as telephone, 0 as userType, null as selected_credential_id,null as household_type, null as one_inch_color_white_photo,null as two_inch_color_blue_photo, null as education,null as marital_status,null as military_status, null as nation, null as native_place,null as politics_status from t_user tu join identity_user iu on tu.ID=iu.id where iu.id not in (select id from tenant_users) and iu.telephone not in (select telephone from tenant_users); /** 租户组织信息 其中, 假设正式站点不会出现重复公司名称的情况, 因此, 先忽略这些重复的问题 */ INSERT INTO tenant_organizations(id, approved_information_last_updated_time, approved_information_note, approved_information_status, code, created_time, industry_code, institutional_code, last_updated_time, logo_path, name, place_of_business_address, place_of_business_province_code, place_of_business_province_name, place_of_business_city_code, place_of_business_city_name, place_of_business_district_code, place_of_business_district_name, place_of_register_address, place_of_register_province_code, place_of_register_province_name,place_of_register_city_code, place_of_register_city_name, place_of_register_district_code, place_of_register_district_name, tenant_user_id) select id,'2021-01-01','',2,id,'2021-01-01',field14,field15,'2021-01-01', k.LOGO_URL,k.NAME, k.FIELD13 as place_of_business_address, SUBSTRING_INDEX(FIELD13,',',1) as place_of_business_province_code, SUBSTRING_INDEX(FIELD13,',',1) as place_of_business_province_name, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1) as place_of_business_city_code, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1) as place_of_business_city_name, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1) as place_of_business_district_code, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1) as place_of_business_district_name, k.FIELD13 as place_of_register_address, SUBSTRING_INDEX(FIELD12,',',1) as place_of_register_province_code, SUBSTRING_INDEX(FIELD12,',',1) as place_of_register_province_name, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1) as place_of_register_city_code, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1) as place_of_register_city_name, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1) as place_of_register_district_code, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1) as place_of_register_district_name, COALESCE((select t2.id from t_user t2 where t2.DOMAINID=k.id and COALESCE(t2.ISDOMAINUSER,'false')='true' limit 1),'') from t_domain k where k.NAME<>'all_users_zone' and k.id not in (select id from tenant_organizations) and not exists(select * from (select NAME,count(1) as ct from t_domain group by NAME) as x where ct>1 and x.name=k.name) ; /** 公司信息 */ INSERT INTO public.tenant_companies(id, unify_social_credit_code, unify_social_credit_code_file) select a.id,COALESCE(b.FIELD11,''),COALESCE(b.FIELD16,'') from tenant_organizations a join t_domain b on a.id=b.ID where a.id not in (select id from tenant_companies); update tenant_companies x set unify_social_credit_code=j.FIELD11, unify_social_credit_code_file=j.FIELD16 from t_domain j where x.id=j.ID and j.FIELD11 is not null and j.FIELD16 is not null; /** 修改部门 */ INSERT INTO public.tenant_departments ( id, code, created_time, display_order, index_tree, last_updated_time, level, name, permission_config, permission_type, status, total_member, organization_id, parent_id) select t.id,COALESCE(t.CODE,''),COALESCE(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,COALESCE(t.INDEXCODE,''),COALESCE(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,t.NAME, null as permission_config,0,1,0,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is null and t.ID not in (select ID from tenant_departments); /** 需要循环执行几次, 类似如下操作进行修改 update t_department set NAME=CONCAT(NAME,'..') where id='yJWAZLGc'; select * from ( select count(1) as ct, NAME, DOMAIN_ID from t_department group by NAME,DOMAIN_ID ) t where t.ct>1; */ INSERT INTO public.tenant_departments ( id, code, created_time, display_order, index_tree, last_updated_time, level, name, permission_config, permission_type, status, total_member, organization_id, parent_id) select id,CODE,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,NAME,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.CODE,COALESCE(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.NAME, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from tenant_departments) ) as k where icount=1 and SUPERIOR in (select id from tenant_departments); INSERT INTO public.tenant_departments ( id, code, created_time, display_order, index_tree, last_updated_time, level, name, permission_config, permission_type, status, total_member, organization_id, parent_id) select id,CODE,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,NAME,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.CODE,COALESCE(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.NAME, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from tenant_departments) ) as k where icount=2 and SUPERIOR in (select id from tenant_departments); INSERT INTO public.tenant_departments ( id, code, created_time, display_order, index_tree, last_updated_time, level, name, permission_config, permission_type, status, total_member, organization_id, parent_id) select id,CODE,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,NAME,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.CODE,COALESCE(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.NAME, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from tenant_departments) ) as k where icount=3 and SUPERIOR in (select id from tenant_departments); INSERT INTO public.tenant_departments ( id, code, created_time, display_order, index_tree, last_updated_time, level, name, permission_config, permission_type, status, total_member, organization_id, parent_id) select id,CODE,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,NAME,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.CODE,COALESCE(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.NAME, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from tenant_departments) ) as k where icount=4 and SUPERIOR in (select id from tenant_departments); INSERT INTO public.tenant_departments ( id, code, created_time, display_order, index_tree, last_updated_time, level, name, permission_config, permission_type, status, total_member, organization_id, parent_id) select id,CODE,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,NAME,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.CODE,COALESCE(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.NAME, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from tenant_departments) ) as k where icount=5 and SUPERIOR in (select id from tenant_departments); INSERT INTO public.tenant_departments ( id, code, created_time, display_order, index_tree, last_updated_time, level, name, permission_config, permission_type, status, total_member, organization_id, parent_id) select id,CODE,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,NAME,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.CODE,COALESCE(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.NAME, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from tenant_departments) ) as k where icount=6 and SUPERIOR in (select id from tenant_departments); INSERT INTO public.tenant_departments ( id, code, created_time, display_order, index_tree, last_updated_time, level, name, permission_config, permission_type, status, total_member, organization_id, parent_id) select id,CODE,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,NAME,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.CODE,COALESCE(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.NAME, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from tenant_departments) ) as k where icount=7 and SUPERIOR in (select id from tenant_departments); INSERT INTO public.tenant_departments ( id, code, created_time, display_order, index_tree, last_updated_time, level, name, permission_config, permission_type, status, total_member, organization_id, parent_id) select id,CODE,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,NAME,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.CODE,COALESCE(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.NAME, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from tenant_departments) ) as k where icount=8 and SUPERIOR in (select id from tenant_departments); INSERT INTO public.tenant_departments ( id, code, created_time, display_order, index_tree, last_updated_time, level, name, permission_config, permission_type, status, total_member, organization_id, parent_id) select id,CODE,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,NAME,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.CODE,COALESCE(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.NAME, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from tenant_departments) ) as k where icount=9 and SUPERIOR in (select id from tenant_departments); INSERT INTO public.tenant_departments ( id, code, created_time, display_order, index_tree, last_updated_time, level, name, permission_config, permission_type, status, total_member, organization_id, parent_id) select id,CODE,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,NAME,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.CODE,COALESCE(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.NAME, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from tenant_departments) ) as k where icount=10 and SUPERIOR in (select id from tenant_departments); /** 职员 先手动修改错误的部门信息 */ update t_user h set DEFAULTDEPARTMENT=(select r.DEPARTMENTID from t_user_department_role_set r where r.USERID=h.ID limit 1); update tenant_employees c set tenant_user_id=(select b.id from tenant_users b join t_user x on b.telephone=x.LOGINNO where x.id=c.id limit 1); /** 设置部门管理员 */ INSERT INTO public.tenant_department_admins(id, created_time, department_id, employee_id, organization_id) select id,'2021-01-01',DEPARTMENTID,USERID,(select u.DOMAINID from t_user u where ID=t.USERID limit 1) from t_user_department_admin_set t where t.ID not in (select l.id from tenant_department_admins l) and t.USERID is not null and USERID in (select id from tenant_employees); /** 默认授权智能人事的应用 */ update t_domain set BIND_APPLICATIONS=REPLACE(BIND_APPLICATIONS,']',',__xkeybkGaZa1SboC9GL1]') where BIND_APPLICATIONS not like '%__xkeybkGaZa1SboC9GL1%' and LENGTH(BIND_APPLICATIONS)>5; update t_domain set BIND_APPLICATIONS=REPLACE(BIND_APPLICATIONS,']','__xkeybkGaZa1SboC9GL1]') where BIND_APPLICATIONS not like '%__xkeybkGaZa1SboC9GL1%' and LENGTH(BIND_APPLICATIONS)>1; update t_domain set BIND_APPLICATIONS='[__xkeybkGaZa1SboC9GL1]' where BIND_APPLICATIONS is null; update t_user set FIELD16=null where FIELD16=''; update t_user set FIELD17=null where FIELD17=''; /** 证件信息 */ INSERT INTO public.tenant_user_credentials(id, created_time, address, credential_type, front_photo, number, reverse_photo, valid_date_from, valid_date_to, tenant_user_id, head_photo, name, selected) select mId,mct,address,credential_type,front_photo,number, reverse_photo,to_date(valid_date_from, 'yyyy-MM-dd'),to_date(valid_date_to, 'yyyy-MM-dd'),mtenant_user_id,head_photo,NAME, selected from ( select min(x.id) as mId,min(x.created_time) as mct,MAX(x.address) AS address,x.credential_type,MAX(x.front_photo) AS front_photo,x.number, MAX(x.reverse_photo) AS reverse_photo, MAX(x.valid_date_from) AS valid_date_from, MAX(x.valid_date_to) AS valid_date_to,min(x.tenant_user_id) as mtenant_user_id, MAX(x.head_photo) AS head_photo,max(x.NAME) as NAME,x.selected from ( select t.id,COALESCE(t.LASTMODIFYTIME,'2021-01-01') as created_time, COALESCE(t.FIELD14,'') as address, (case t.FIELD11 when '居民身份证（户口簿）' then 0 when '居民身份证' then 0 when '警号' then 6 when '香港特区护照/身份证' then 1 when '香港特区护照/身份证明' then 1 when '澳门特区护照/身份证' then 2 when '澳门特区护照/身份证明' then 2 when '台湾居民来往大陆通行证' then 3 when '外国护照' then 4 when '退伍军人证' then 5 else -1 end)as credential_type, COALESCE(t.FIELD18,'') as front_photo,COALESCE(t.FIELD12,'')as number, COALESCE(t.FIELD19,'') as reverse_photo, COALESCE(t.FIELD16,'1900-01-01') as valid_date_from,COALESCE(t.FIELD17,'yyyy-MM-dd') as valid_date_to, (select k.id from tenant_users k where k.telephone=t.loginno limit 1) as tenant_user_id, COALESCE(t.FIELD15,'') as head_photo, t.NAME ,1 as selected from t_user t where t.FIELD11 is not null ) as x where x.id not in (select id from tenant_user_credentials) and x.credential_type>-1 and COALESCE(x.number,'')<>'' and number not in (select number from tenant_user_credentials) group by credential_type,number,x.selected ) as hg ; UPDATE tenant_users x set selected_credential_id=(select k.id from tenant_user_credentials k where k.tenant_user_id=x.id limit 1); -- update tenant_user_credentials set valid_date_from='1900-01-01' where valid_date_from='0000-00-00'; -- update tenant_user_credentials set valid_date_to='3050-01-01' where valid_date_to='0000-00-00'; /** 默认授权智能人事 */ insert into t_user_department_role_set(id,DEPARTMENTID,USERID,ROLEID) select id, (select k.DEPARTMENTID from t_user_department_role_set k where k.USERID=u.ID limit 1) as DepartId,id as userId,'__2uz0l9F3qiXuMQZmma0' from t_user u where u.DOMAINID in (select id from t_domain d where d.BIND_APPLICATIONS like '%__xkeybkGaZa1SboC9GL1%' ) AND NOT EXISTS(SELECT 1 FROM t_user_department_role_set t where t.ID=id) and ID not in (select COALESCE(USERID,'xxx') from t_user_department_role_set where ROLEID='__2uz0l9F3qiXuMQZmma0'); /** 授权有问题的数据-保证组织管理员拥有智能人事的应用 */ insert into t_user_department_role_set(id,DEPARTMENTID,USERID,ROLEID) select id, (select k.DEPARTMENTID from t_user_department_role_set k where k.USERID=u.ID limit 1) as DepartId,id as userId,'__2uz0l9F3qiXuMQZmma0' from t_user u where u.DOMAINID in (select id from t_domain d where d.BIND_APPLICATIONS like '%__xkeybkGaZa1SboC9GL1%' ) and u.ID not in ( select USERID from t_user_department_role_set) and (u.ISDOMAINUSER='true' or u.ISDOMAINUSER='1'); update tenant_users x set cid=(select k.FIELD24 from t_user k where k.id=x.id limit 1), wechat=(select k.field22 from t_user k where k.id=x.id limit 1); update tenant_companies x set unify_social_credit_code=j.FIELD11, unify_social_credit_code_file=j.FIELD16 from t_domain j where x.id=j.ID and j.FIELD11 is not null and j.FIELD16 is not null; update tenant_organizations x set place_of_business_address=k.FIELD13, place_of_business_province_code=SUBSTRING_INDEX(FIELD13,',',1), place_of_business_province_name= SUBSTRING_INDEX(FIELD13,',',1), place_of_business_city_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1), place_of_business_city_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1), place_of_business_district_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1), place_of_business_district_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1), place_of_register_address = k.FIELD13, place_of_register_province_code = SUBSTRING_INDEX(FIELD12,',',1), place_of_register_province_name = SUBSTRING_INDEX(FIELD12,',',1), place_of_register_city_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1) , place_of_register_city_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1), place_of_register_district_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1), place_of_register_district_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1), industry_code=k.FIELD14, institutional_code=k.FIELD15 from t_domain k where x.id=k.ID and x.place_of_business_address is null; /** 区域信息 */ update tenant_organizations k set place_of_business_province_code=(select x.id from sys_region x where x.region_full_name=k.place_of_business_province_name limit 1); update tenant_organizations k set place_of_business_city_code=(select x.id from sys_region x where x.region_full_name=concat(k.place_of_business_province_name,k.place_of_register_city_name) limit 1); update tenant_organizations k set place_of_business_district_code=(select x.id from sys_region x where x.region_full_name=concat(k.place_of_business_province_name,k.place_of_register_city_name,k.place_of_business_district_name) limit 1); update tenant_organizations k set place_of_register_province_code=(select x.id from sys_region x where x.region_full_name=k.place_of_register_province_name limit 1); update tenant_organizations k set place_of_register_city_code=(select x.id from sys_region x where x.region_full_name=concat(k.place_of_register_province_name,k.place_of_register_city_name) limit 1); update tenant_organizations k set place_of_register_district_code=(select x.id from sys_region x where x.region_full_name=concat(k.place_of_register_province_name,k.place_of_register_city_name,k.place_of_register_district_name) limit 1);