使用“WITH"和“更新"同一 SQL 查询中的语句 [英] Using "WITH" and "UPDATE" statements in the same SQL query

问题描述

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

I have a table that I need to update with some data from an Excel spreadsheet. I am thinking of a query along these lines:

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 语句中，即

But Oracle doesn't seem to like having a "WITH" and "UPDATE" statement in the same query. I get an error saying "missing SELECT keyword". I have found out that I can put the temp table definition within the SELECT statement, i.e.

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

但是在查询中间定义这样的表是非常糟糕的代码.我只是想想就畏缩.必须有更好的方法来做到这一点.我应该设置一个全局临时表吗?或者我只是缺少一些简单的语法，可以使这项工作以原始方式工作?

But that is horribly, horribly messy code to define a table like that right in the middle of the query. I cringe just thinking about it. There has to be a better way to do this. Should I set up a global temporary table? Or am I just missing some simple syntax that would make this work the original way?

推荐答案

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

You can use a with clause in an update; you just have to do it in the right place:

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 子句:

However, you're probably wanting to only update rows that exist in the temp subquery, so you would need an additional where clause:

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 语句:

Alternatively, use a MERGE statement:

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.

N.B. you have to join to the actual table in the source query of the merge statement because you're trying to update the column that's being joined on, which you can't do in a merge statement - hence I've switched the merge join to join on mytable.rowid.

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

You'd have to test both statements to see which one is most performant on yor data.

这篇关于使用“WITH"和“更新"同一 SQL 查询中的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！