sql - 在同一 SQL 查询中使用 "WITH"和 "UPDATE"语句

Question

我有一个表格，需要使用 Excel 电子表格中的一些数据进行更新。我正在考虑以下方面的查询:

WITH temp AS(
(SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
(SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
)
UPDATE mytable
   SET name = (SELECT newvalue FROM temp WHERE mytable.name = temp.oldvalue)

但是 Oracle 似乎不喜欢在同一个查询中同时使用“WITH”和“UPDATE”语句。我收到一条错误消息，提示“缺少 SELECT 关键字”。我发现我可以将临时表定义放在 SELECT 语句中，即

 SET name = (SELECT newvalue FROM (
         (SELECT 'abcd' AS oldvalue, 'defg' AS  newvalue FROM dual) UNION
         (SELECT .....)
          ) temp WHERE mytable.name = temp.oldvalue)

但是，在查询中间定义这样一个表的代码非常非常困惑。我只是想着它就畏缩。必须有更好的方法来做到这一点。我应该设置一个全局临时表吗？还是我只是缺少一些简单的语法来使这项工作以原始方式进行？

Answer 1

您可以在更新中使用 with 子句；你只需要在正确的地方做:

UPDATE mytable
   SET name = (WITH temp AS((SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
                            (SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
                           )
               SELECT newvalue
               FROM   temp
               WHERE  mytable.name = temp.oldvalue);

但是，您可能只想更新临时子查询中存在的行，因此您需要一个额外的 where 子句:

UPDATE mytable
   SET name = (WITH temp AS((SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
                            (SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
                           )
               SELECT newvalue
               FROM   temp
               WHERE  mytable.name = temp.oldvalue)
WHERE  EXISTS (WITH temp AS((SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
                            (SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
                           )
               SELECT NULL
               FROM   temp
               WHERE  mytable.name = temp.oldvalue);

或者，使用 MERGE 语句:

merge into mytable tgt
  using (WITH temp AS((SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
                      (SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
                     )
         SELECT mytable.rowid r_id,
                temp.newvalue
         FROM   temp
         inner  join mytable on mytable.name = temp.oldvalue) src
    on (tgt.rowid = src.r_id)
when matched then
update set tgt.name = src.newvalue;

注意您必须在合并语句的源查询中连接到实际表，因为您正在尝试更新正在连接的列，这是您不能在合并语句中执行的 - 因此我已将合并连接切换为加入 mytable.rowid。

您必须测试这两个语句，以查看哪个语句对您的数据性能最佳。