一、问题:
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。