MySQL根据一个字段批量更新关联字段——问题记录

一、问题：

base_user基础表中需要新增一个字段is_at_school（是否在校：0否 1是）标识人员是否在校，该字段与status_code（人员状态编码）相关联，关联关系如下图，现需要在新增字段后历史数据的该字段值要更新成相应的值。

二、解决方案：

2.1 先根据status_code查出数据id，再根据id更新is_at_school

-- 更新在校状态的‘是否在校’为是
UPDATE base_user
SET is_at_school = 1
WHERE
	id IN (
		SELECT
			ids.id
		FROM
		    (
			    SELECT
				    id
			    FROM
			    	base_user
			    WHERE
			    	`status_code` IN ('04', '10', '11')
		    ) AS ids
	);

-- 更新非在校状态的‘是否在校’为否
UPDATE base_user
SET is_at_school = 0
WHERE
	id IN (
		SELECT
			ids.id
		FROM
			(
			    SELECT
				    id
			    FROM
			    	base_user
			    WHERE
			    	`status_code` IN ('01','02','03','05','06','07','08','09')
			) AS ids
	);

要点记录：

用in指定范围时，若该范围是查询出来的结果集，那么需要把结果集当作一个表，自我查询一遍，不然会报错“1093 - You can't specify target table 'result' for update in FROM clause ”

格式为：SELECT   ids.id   FROM   (结果集)ids

2.2 case when搜索用法

UPDATE base_user
SET is_at_school = (
	CASE
	WHEN `status_code` IN ('04', '10', '11') THEN
		1
	WHEN `status_code` ('01', '02', '03', '05','06', '07', '08', '09') THEN
		0
	END
)

要点记录：

CASE WHEN [expr] THEN [result1]…ELSE [default] END：搜索函数可以写判断、范围指定等表达式，并且搜索函数只会返回第一个符合条件的值，其他case被忽略，后面也可以不加ELSE。

该搜索函数作用于涉及到的整个集合中的对应字段。

补充记忆：

case when简单函数用法： CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END  它只会返回第一个符合条件的值，其他case被忽略，后面也可以不加ELSE。

参考：https://dev.mysql.com/doc/refman/5.7/en/case.html