工作中有需求要将MySQL数据库某表的数据做update,显少涉及MySQL数据库的工作,略显笨拙,baidu之后,完成了工作,记下以备后用:
- 单表update涉及多次自连接查询
SQL> update tb_jyx_sys_area a set a.areaFullName= (select b.fullname from (select a1.areaId,CONCAT(a2.areaFullName,'/',a1.areaName) fullname from tb_jyx_sys_area a2,tb_jyx_sys_area a1 where a1.`level` = 3 and a1.parentId = a2.areaId)b where b.areaid=a.areaId) where a.`level`=3;
2.单表update涉及JION、group by,having
SQL> update tb_jyx_sys_area b join (select a.areaId FROM tb_jyx_sys_area a left join tb_jyx_sys_area a1 on a.areaId = a1.parentId GROUP BY a.areaId,a.areaName having count(a1.areaId) = 0)c on b.areaid=c.areaId set b.isTerminal=1 ;