mysql-多表关联更新数据

mysql-多表关联更新数据

场景:需要根据部门(department)表的city_id更新对应user(用户表)的city_id字段的值,用户表与部门表的逻辑外键(user表的dept_id)-至于为什么这样做你别管。user-用户表 department-部门表 enabled-是否已删除

UPDATE  `user` u
INNER  JOIN
(SELECT u.id,d.city_id FROM department d INNER JOIN `user` u on u.dept_id=d.id  WHERE u.enabled=0 and d.enabled=0) t
on t.id=u.id
SET u.city_id=t.city_id
WHERE
u.enabled=0
and u.dept_id is not null;

示例 2：

UPDATE order o 
INNER JOIN 
product_item  i ON o.itemId=i.id
SET o.salePrice=i.salePrice