批量更新关联的sql

参考：

关联更新
先说方法
update table1
join table2 on table1.关联字段=table2.关联字段
set table1.要改变的字段=table2.对应的字段

这个table2经常是一张表的聚合后的查询结果

应用场景
项目中经常会有多张表的各种关联,一对多,多对多等关系很多,导致查询一张表格的东西要关联很多表,速度很慢;
这时候考虑添加一个冗余字段,减少查询时表的关联;但是已经有的数据,需要更新一下,一个个更新肯定要累死,所以这时候就需要批量关联更新

举个栗子
有两张表:
dept:

CREATE TABLE `dept` (
  `dept_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `emp_count` int(11) DEFAULT NULL,
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

employee

CREATE TABLE `employee` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `dept_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

dept中的数据:

mysql> select * from dept;
+---------+--------+-----------+
| dept_id | name   | emp_count |
+---------+--------+-----------+
|       1 | 开发   |      NULL |
|       2 | 测试   |      NULL |
|       3 | 销售   |      NULL |
+---------+--------+-----------+

employee中的数据:

mysql> select * from employee;
+--------+---------+--------+
| emp_id | dept_id | name   |
+--------+---------+--------+
|      1 |       1 | 小白   |
|      2 |       1 | 中白   |
|      3 |       1 | 大白   |
|      4 |       2 | 小黑   |
|      5 |       2 | 中黑   |
|      6 |       3 | 大黄   |
+--------+---------+--------+

现在想根据各部门的员工数量,如果部门很多,一个一个更新怕是要累死
批量更新到部门表的emp_count字段上,就需要这么做:

update dept 
join (select COUNT(emp_id) as n,dept_id from employee GROUP BY dept_id) as e
on e.dept_id = dept.dept_id
set emp_count = e.n;

关联删除
有了关联更新,肯定有时候也想关联删除了

delete from t1 where 条件
delete t1 from t1 where 条件
delete t1 from t1,t2 where 条件
delete t1,t2 from t1,t2 where 条件

语法:

delete A
from A
inner join B
on A.CID=B.CID
where B.cp = 2 and A.x=xx;

我自己这边使用的例子

update  schedule_detail a
INNER JOIN (select schedule_code,material_code,move_out from schedule_detail  where schedule_code='ANE24A0014'  and move_out>0) b 
on b.material_code=a.material_code 
set a.move_in = b.move_out
where a.schedule_code='ANE24A0012-1'  and move_in<0