INSERT IGNORE INTO crm.crm_customer_public ( `cust_code`, `user_code`, `user_name`, `store_code`, `store_name`, `type`, `fell_time` ) SELECT l.cust_code, d.user_code, d.chinese_name, d.dept_code, d.dept_name, 1, "2020-09-04 12:12:12" FROM crm.crm_customer_user_rel l ,(select a.user_code,u.chinese_name,b.dept_code,b.dept_name from vip.vip_department b, vip.vip_department_user_rel a , vip.vip_user u , vip.sys_role r ,vip.sys_user_role_rela rr where b.dept_code=a.dept_code and a.user_code=u.user_code and u.user_code=rr.user_no and rr.role_no=r.role_no and r.role_no="ROLE180300039" and b.dept_type=1 group by b.dept_code) d WHERE l.user_code = 'USER1910076906' AND d.dept_name LIKE "深圳店%" AND NOT EXISTS ( SELECT c1.cust_code FROM vip.mgt_emp_resign_order_copy c1 WHERE c1.cust_code = l.cust_code );
update IGNORE crm.crm_customer_user_rel l set user_role = 2,cust_type=2,cust_user_status=0,update_time = "2020-09-04 12:12:12" where l.user_code = 'USER1910076906' and NOT EXISTS (select c1.cust_code from vip.mgt_emp_resign_order_copy c1 where c1.cust_code = l.cust_code);
UPDATE IGNORE crm.crm_lead l, (select a.user_code,u.chinese_name,b.dept_code,b.dept_name from vip.vip_department b, vip.vip_department_user_rel a , vip.vip_user u , vip.sys_role r ,vip.sys_user_role_rela rr where b.dept_code=a.dept_code and a.user_code=u.user_code and u.user_code=rr.user_no and rr.role_no=r.role_no and r.role_no="ROLE180300039" and b.dept_type=1 group by b.dept_code) d SET l.follower_code = d.user_code,l.follower_name = d.chinese_name,l.follower_store_code=d.dept_code,l.follower_store_name = d.dept_name ,l.update_time="2020-09-04 12:12:12" WHERE l.follower_code IN ( "USER1910076906" ) AND d.dept_name LIKE "深圳店%" AND not exists (select id from vip.mgt_emp_resign_order_copy c1 where c1.lead_code = l.lead_code);
UPDATE IGNORE crm.crm_project_user_rel r,(select a.user_code,u.chinese_name,b.dept_code,b.dept_name from vip.vip_department b, vip.vip_department_user_rel a , vip.vip_user u , vip.sys_role r ,vip.sys_user_role_rela rr where b.dept_code=a.dept_code and a.user_code=u.user_code and u.user_code=rr.user_no and rr.role_no=r.role_no and r.role_no="ROLE180300039" and b.dept_type=1 group by b.dept_code) d SET r.user_code = d.user_code, r.user_name = d.chinese_name, r.store_code = d.dept_code, r.store_name = d.dept_name, r.update_time = "2020-09-04 12:12:12" WHERE r.user_code in ( "USER1910076906") AND d.dept_name LIKE "深圳店%" and not exists ( SELECT id FROM vip.mgt_emp_resign_order_copy c1 WHERE c1.project_code = r.project_code );