如何利用CTE（公共表表达式）实现数据表UPDATE操作？

一、CTE方案 #

你原语句无法执行的原因是未建立目标表与CTE的关联上下文，Oracle无法直接识别vars.n的引用。正确写法需要通过子查询从CTE中提取变量值：

WITH vars AS (SELECT 5 AS n FROM dual)
UPDATE test
SET data = data * (SELECT n FROM vars),
    more = more * (SELECT n FROM vars);

若需同时使用多个变量，可通过行子查询批量赋值：

WITH vars AS (SELECT 5 AS n, 10 AS m FROM dual)
UPDATE test t
SET (t.data, t.more) = (SELECT t.data * v.n, t.more * v.m FROM vars v);

二、子查询替代方案 #

如果CTE方案不适用，子查询方式同样满足需求，适配单变量或多变量场景：

单变量场景

UPDATE test
SET data = data * (SELECT 5 FROM dual),
    more = more * (SELECT 5 FROM dual);

多变量场景

UPDATE test t
SET (t.data, t.more) = (SELECT t.data * v.n, t.more * v.m FROM (SELECT 5 AS n, 10 AS m FROM dual) v);

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