Oracle TOAD中使用With CTE执行UPDATE时遇‘Found UPDATE: Expecting:(SELECT..)’错误

错误原因 #

你使用的是SQL Server风格的UPDATE ... FROM语法，Oracle并不支持这种写法。Oracle对CTE结合更新操作有特定的语法要求，这就是你收到Found 'UPDATE': Expecting:(SELECT -or-,….. CYCLE or SEARCH)错误的原因。

修正后的代码（使用MERGE语句） #

Oracle推荐使用MERGE语句来实现这类基于CTE的更新操作，这是最简洁且符合Oracle语法规范的方式：

WITH CTE AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY ID) + 99 AS New_id
    FROM table_1
    WHERE ID BETWEEN 155662 AND 166046
)
MERGE INTO table_1 t
USING CTE c
ON (t.id = c.id)
WHEN MATCHED THEN
    UPDATE SET t.id = c.new_id;

另一种写法（UPDATE关联子查询） #

如果你更倾向于使用UPDATE语句，可以将CTE逻辑转换为嵌套关联子查询：

UPDATE table_1 t
SET t.id = (
    SELECT rn + 99
    FROM (
        SELECT id, ROW_NUMBER() OVER (ORDER BY ID) AS rn
        FROM table_1
        WHERE ID BETWEEN 155662 AND 166046
    ) c
    WHERE c.id = t.id
)
WHERE t.id BETWEEN 155662 AND 166046;

注意事项 #

• 由于你已经将ID列从GENERATED ALWAYS改为GENERATED BY DEFAULT，上述更新操作不会触发自增列的修改限制，可以正常执行。
• 更新完成后，你可将这些行插入目标表，再通过DELETE FROM table_1 WHERE id BETWEEN 100 AND 10483（注：10483为原ID范围总行数+99的结果）删除table_1中的对应行。

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