业务实现

1. 连表修改

UPDATE 
表1 as o1 , 
(
  SELECT temp1.objId, temp1.objName, temp1.oDepId, temp1.oDepName, temp1.newDepName, dd.id as depId, dd.name as depName 
  FROM
  (SELECT o.id as objId, o.name as objName, o.depId as oDepId, d.name as oDepName, CONCAT('上海市',d.name) as newDepName 
   FROM 表1 o INNER JOIN 表3 d ON o.depId = d.id) as temp1 LEFT JOIN 表2 dd ON temp1.newDepName = dd.name
) as temp2 SET o1.depId = temp2.depId WHERE o1.id = temp2.objId AND temp2.depId != '' AND temp2.depId IS NOT NULL;

2. 存储过程-sql中循环操作

DELIMITER $$
DROP PROCEDURE if EXISTS dealCooperateDepIds;    # 如果存在dealCooperateDepIds存储过程则删除
CREATE procedure dealCooperateDepIds() # 创建无参存储过程，名称为dealCooperateDepIds
BEGIN
    DECLARE i INT;  # 申明变量
    SET i = (SELECT count(*)
                FROM
                表1 p
                LEFT JOIN 
                表2 d
                ON INSTR(p.cooperateDepIds,d.id) > 0
                WHERE d.id != '' AND d.id IS NOT NULL);  # 变量赋值
    WHILE i != 0 DO # 结束循环的条件
        UPDATE 表1 cp,
            (
            SELECT p.id, p.taskName, p.cooperateDepIds, d.id as did, d.`name` as dname, d.new_id, d.new_name
            FROM
            表1 p
            LEFT JOIN 
            表2 d
            ON INSTR(p.cooperateDepIds,d.id) > 0
            WHERE d.id != '' AND d.id IS NOT NULL
            ) as temp SET cp.cooperateDepIds = REPLACE(cp.cooperateDepIds,temp.did,temp.new_id) WHERE cp.id = temp.id;  # 修改数据
        SET i = (SELECT count(*)
                FROM
                表1 p
                LEFT JOIN 
                表2 d
                ON INSTR(p.cooperateDepIds,d.id) > 0
                WHERE d.id != '' AND d.id IS NOT NULL);    # 循环一次,i更新
    END WHILE;  # 结束while循环
END  $$ # 结束定义语句
DELIMITER ; # 重新将分隔符设置为;
CALL dealCooperateDepIds();    #调用存储过程