批量刷数据2

最新推荐文章于 2023-08-15 17:37:13 发布

原创最新推荐文章于 2023-08-15 17:37:13 发布 · 220 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一种通过SQL更新语句批量修改数据库中特定记录的方法，包括更新订单、商机编码、工程编码以及相关人员关联等信息。

-- 批量更新有订单的数据

-- 批量刷商机编码
UPDATE IGNORE vip.mgt_emp_resign_order_copy o1 SET o1.lead_code = ( SELECT o2.lead_code FROM crm.crm_lead_order_rel o2 WHERE o1.order_code = o2.order_code ) WHERE EXISTS ( SELECT o3.id FROM crm.crm_lead_order_rel o3 WHERE o1.order_code = o3.order_code ) AND o1.old_cust_mgr_code IN ( 'USER180301042', 'USER180301050', 'USER180301075', 'USER180301162', 'USER180301179', 'USER1810017141', 'USER1903031205', 'USER1904034035', 'USER1910076906', 'USER2004137108', 'USER2004137129', 'USER122860', 'USER2005144996', 'USER2005144997', 'USER2005145006', 'USER2005145007', 'USER2005145009', 'USER2005145017', 'USER130014', 'USER2005146576', 'USER2005146587' );
-- 批量刷工程编码
UPDATE IGNORE vip.mgt_emp_resign_order_copy o1 SET o1.project_code = ( SELECT o2.project_code FROM vip.oms_order_awp o2 WHERE o1.order_code = o2.order_code ) WHERE EXISTS ( SELECT o3.id FROM vip.oms_order_awp o3 WHERE o1.order_code = o3.order_code ) AND o1.old_cust_mgr_code IN ( 'USER180301042', 'USER180301050', 'USER180301075', 'USER180301162', 'USER180301179', 'USER1810017141', 'USER1903031205', 'USER1904034035', 'USER1910076906', 'USER2004137108', 'USER2004137129', 'USER122860', 'USER2005144996', 'USER2005144997', 'USER2005145006', 'USER2005145007', 'USER2005145009', 'USER2005145017', 'USER130014', 'USER2005146576', 'USER2005146587' ); 
-- 批量刷商机关联的客户经理
UPDATE IGNORE crm.crm_lead l, vip.mgt_emp_resign_order_copy c, vip.vip_department b, vip.vip_department_user_rel a, vip.vip_user u SET l.follower_code = c.now_cust_mgr_code, l.follower_name = u.chinese_name, l.follower_store_code = b.dept_code, l.follower_store_name = b.dept_name, l.update_time = "2020-09-04 11:11:11" WHERE b.dept_code = a.dept_code AND a.user_code = u.user_code AND b.dept_type = 1 AND a.is_part_time = 0 AND c.lead_code = l.lead_code AND c.now_cust_mgr_code = u.user_code AND l.follower_code IN ( 'USER180301042', 'USER180301050', 'USER180301075', 'USER180301162', 'USER180301179', 'USER1810017141', 'USER1903031205', 'USER1904034035', 'USER1910076906', 'USER2004137108', 'USER2004137129', 'USER122860', 'USER2005144996', 'USER2005144997', 'USER2005145006', 'USER2005145007', 'USER2005145009', 'USER2005145017', 'USER130014', 'USER2005146576', 'USER2005146587' );
--批量刷工程关联的客户经理
UPDATE IGNORE crm.crm_project_user_rel r, vip.mgt_emp_resign_order_copy c, vip.vip_department b, vip.vip_department_user_rel a, vip.vip_user u SET r.user_code = c.now_cust_mgr_code, r.user_name = u.chinese_name, r.store_code = b.dept_code, r.store_name = b.dept_name, r.update_time = "2020-09-04 11:11:11" WHERE b.dept_code = a.dept_code AND a.user_code = u.user_code AND b.dept_type = 1 AND a.is_part_time = 0 AND c.now_cust_mgr_code = u.user_code AND r.project_code = c.project_code AND r.user_code IN ( 'USER180301042', 'USER180301050', 'USER180301075', 'USER180301162', 'USER180301179', 'USER1810017141', 'USER1903031205', 'USER1904034035', 'USER1910076906', 'USER2004137108', 'USER2004137129', 'USER122860', 'USER2005144996', 'USER2005144997', 'USER2005145006', 'USER2005145007', 'USER2005145009', 'USER2005145017', 'USER130014', 'USER2005146576', 'USER2005146587' );