使用"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关键字".我发现我可以将temp表定义放在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);

但是，您可能只想更新temp子查询中存在的行，因此您将需要一个附加的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;

您必须在merge语句的源查询中连接到实际表，因为您正试图更新正在连接的列，而这在merge语句中是无法做到的-因此，我将合并连接切换为加入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屋！