如何基于不同数据类型列的数学运算结果更新新增空列

问题根因 #

报错核心是colA（varchar类型）中存在无法转换为数值的非数字内容（如空字符串、空格、特殊字符、中文等）。UPDATE操作需要对全表所有行生效，只要任意一行的类型转换失败就会直接终止并抛出错误；而SELECT查询要么你测试的刚好是可转换的行，要么部分数据库的SELECT隐式转换容错性更高，会将转换失败的行返回NULL而非抛出错误，因此看起来能正常运行。
你之前尝试的写法存在逻辑错误：COALESCE(colA::numeric::text,'') * 2 中，首先尝试将colA转numeric，这一步遇到非数字内容就会直接报错，后续转text、COALESCE赋值空串的逻辑完全不会生效，就算生效空串和数字做乘法也会触发类型错误。

解决方案 #

你使用的::类型转换语法符合PostgreSQL、Redshift等数据库的规范，可使用正则先判断字段内容是否为合法数字，再做转换运算：

UPDATE tablename 
SET colB = CASE 
    -- 正则匹配正负整数、小数格式的合法数字
    WHEN colA ~ '^-?[0-9]+(\.[0-9]+)?$' THEN colA::numeric * 2 
    -- 非合法数字的行赋值为NULL，避免报错
    ELSE NULL 
END;

如果你使用的是MySQL，可使用如下写法：

UPDATE tablename 
SET colB = IF(
    colA REGEXP '^-?[0-9]+(\\.[0-9]+)?$',
    CAST(colA AS DECIMAL(10,2)) * 2,
    NULL
);

可选前置校验 #

如果你需要先排查哪些行存在非数字的脏数据，可执行如下查询：

-- PostgreSQL/Redshift 版本
SELECT colA FROM tablename WHERE colA !~ '^-?[0-9]+(\.[0-9]+)?$';

-- MySQL 版本
SELECT colA FROM tablename WHERE colA NOT REGEXP '^-?[0-9]+(\\.[0-9]+)?$';

内容的提问来源于stack exchange，提问作者RustyShackleford