update case when操作出现Column ‘XXX‘ cannot be null的问题解决
1、背景
在利用update case when批量更新数据的时,mysql报错Column ‘XXX‘ cannot be null。原sql为:
UPDATE 表A
SET reason =
CASE
serial_id
WHEN 17 THEN 'x'
WHEN 18 THEN 'y'
END
WHERE
id = 44
2、问题原因
表字段reason的定义是NOT NULL,执行该sql时,会先根据where条件查出待操作数据,比如筛选出3条数据,但我case when的情况只有2个,导致剩余1条数据自己未处理,被更新为null,违背了字段定义。
3、解决方式
- 修改字段定义,不推荐
- 修改sql,增加else,如:
UPDATE 表A
SET reason =
CASE
serial_id
WHEN 17 THEN 'x'
WHEN 18 THEN 'y'
ELSE reason
END
WHERE
id = 44
where筛出3条,when处理2条,剩余1条更新为原值,总共更新3条。
为防止无效更新的数量太多,必须要限制筛选数据的数量。
4、补充SQL的执行顺序
从from开始,每一步都生成一个虚拟表,并作为下一步的输入:
- from:笛卡尔积
- on:过滤
- join:添加外部行
- where:过滤
- group by:分组
- agg_fun:聚合计算
- with:应用rollup或cube
- having:过滤聚合值
- select:选出指定列
- distinct:去重
- order by:排序
- limit/offset:返回指定行