为什么我不能使用 With 关键字同时更新多个列?

2022-01-17 00:00:00 sql-update sql oracle11g oracle

我有一个如下所示的更新语句，它工作正常，我在子查询中使用了一个 with 语句来大大提高性能，但由于某种原因，我不允许从同一个表中添加一个额外的列来更新.

I have an update statement shown below that works fine, I used a with statement in the subquery to greatly improve performance but for some reason I'm not allowed to add an additional column from the same table to update.

作品:

UPDATE Table_A SET (Col_One) = (WITH OneValue AS (SELECT DISTINCT t.Col_One
                                                  FROM Table_Two t, Table_A a
                                                  WHERE t.id = a.New_Id))
                                SELECT Col_One FROM OneValue);

我想要做的只是包括另一个列，也像这样从 table_two 更新

What I'd like to do is just include another column to update also from table_two like this

UPDATE Table_A SET (Col_One, Col_Two) = (WITH OneValue AS (SELECT DISTINCT t.Col_One, T.Col_two
                                                  FROM Table_Two t, Table_A a
                                                  WHERE t.id = a.New_Id))
                                SELECT Col_One, Col_Two FROM OneValue);

但我得到 ora-01767 更新集表达式必须是子查询.我理解这个错误，但看不到我是如何生成它的.非常感谢任何帮助.

but I get ora-01767 update set expression must be a subquery. I understand this error but fail to see how I'm generating it. Any help is greatly appreciated.

提前致谢.

推荐答案

这似乎可行(无论如何，它通过使用 DUAL 进行简单查询):

This appears to work (it did with a simple query using DUAL anyway):

UPDATE Table_A SET (Col_One, Col_Two) = (select col_one, col_two from
                                          (WITH OneValue AS (SELECT DISTINCT t.Col_One, T.Col_two
                                                  FROM Table_Two t, Table_A a
                                                  WHERE t.id = a.New_Id))
                                           SELECT Col_One, Col_Two FROM OneValue)
                                        );

至于子查询以WITH"开头为什么不起作用，我只能想象是因为Oracle SQL的设计者没有预料到这种用法.

As for why it doesn't work if the subquery starts with "WITH", I can only imagine that it is because the designers of Oracle SQL hadn't anticipated this usage.

相关文章