Oracle SQL批量更新多行含重复数据的表，提取数字部分

错误原因分析 #

你触发的ORA-01427错误是因为子查询select regexp_replace(column1, '[^0-9]', '') from tableX返回了多行结果，而SET column1 =需要的是当前行对应的单个值，子查询未限定行范围，导致数据库无法确定要赋值的具体内容。

正确的更新方法 #

1. 单列更新 #

无需嵌套子查询，直接在SET子句中调用REGEXP_REPLACE函数处理当前行的列值：

UPDATE tableX
SET column1 = REGEXP_REPLACE(column1, '[^0-9]', '');

2. 同时更新两列 #

如果要一次性处理两列，可在SET子句中同时指定两个列的处理逻辑：

UPDATE tableX
SET column1 = REGEXP_REPLACE(column1, '[^0-9]', ''),
    column2 = REGEXP_REPLACE(column2, '[^0-9]', '');

3. 优化：仅处理包含非数字的行（可选） #

如果表中部分行已是纯数字，可添加WHERE条件减少不必要的更新：

UPDATE tableX
SET column1 = REGEXP_REPLACE(column1, '[^0-9]', ''),
    column2 = REGEXP_REPLACE(column2, '[^0-9]', '')
WHERE REGEXP_LIKE(column1, '[^0-9]') 
   OR REGEXP_LIKE(column2, '[^0-9]');

补充说明 #

REGEXP_REPLACE(column, '[^0-9]', '')的作用是匹配所有非数字字符并替换为空，从而提取列中的纯数字部分。如果你的数据中数字部分并非连续（比如'100a0 -- 文本'），这个方法会把所有数字拼接成'1000'；若只需要提取开头的连续数字，可改用：

REGEXP_SUBSTR(column1, '^[0-9]+')

两种方法处理'1000 -- 一些文本'时结果一致，但处理带非数字间隔的字符串时会有区别，可根据实际数据选择。

内容的提问来源于stack exchange，提问作者João Costa