批量更新字符串列表字段

数据库QBS.QBS_DEP_ROLE如下，

引用的workid所在表QBS.QBS_WORK

现在想更新QBS_DEP_ROLE表下所有worklist中含有workid2和4列（对应业务意义为无这两个工作区访问权限）

ibatis处理如下：

 

 <update id="updateDepRoles" parameterType="list"> 
update QBS.QBS_DEP_ROLE as r ,(select e.workList,e.depRoleId ,k.workId from QBS.QBS_DEP_ROLE as e, QBS.QBS_WORK k where workList like CONCAT('%',k.workId,',%') and k.workId in
 <foreach collection="list" item="item" open="(" separator="," close=")">
         #{item}
        </foreach>
) as w
SET r.WORKLIST = (select INSERT(w.WORKLIST, LOCATE(w.workId ,w.WORKLIST), LENGTH(w.workId)+1, ''))
WHERE r.depRoleId = w.depRoleId
</update>

 

解释：

 

(select e.workList,e.depRoleId ,k.workId from QBS.QBS_DEP_ROLE as e, QBS.QBS_WORK k where workList like CONCAT('%',k.workId,',%') ------ 临时表中worklist中有workid（因为要删除的工作区可能是多个，比如2和4，有的worklist中可能只有2，但冗余会有workid 4）

and k.workId in  -------workid是要删除的
 <foreach collection="list" item="item" open="(" separator="," close=")">
         #{item}
        </foreach>
) as w
SET r.WORKLIST = (select INSERT(w.WORKLIST, LOCATE(w.workId ,w.WORKLIST), LENGTH(w.workId)+1, ''))   将原WORKLIST替换为去掉workid的WORKLIST

WHERE r.depRoleId = w.depRoleId ----哪个部门的角色下的