可以将 DB2 WITH 语句用作 UPDATE 或 MERGE 的一部分吗? [英] Can a DB2 WITH statement be used as part of an UPDATE or MERGE?

问题描述

我需要更新数据库表中的一些行.我如何识别要更新的行涉及一系列复杂的语句，我设法将它们归结为一系列 WITH 语句.现在我有了正确的数据值，我需要更新表格.

I need to update some rows in a DB table. How I identify the rows to be updated involved a series of complicated statements, and I managed to boil them down to a series of WITH statements. Now I have the correct data values, I need to update the table.

由于我设法通过 WITH 语句获取这些值，因此我希望在 UPDATE/MERGE 中使用它.一个简化的例子如下:

Since I managed to get these values with a WITH statement, I was hoping to use it in the UPDATE/MERGE. A simplified example follows:

with data1
(
    ID_1
)
as
(
    Select ID
    from  ID_TABLE
    where ID > 10
)
,
cmedb.data2
(
     MIN_ORIGINAL_ID
    ,OTHER_ID
)
as
(
    Select min(ORIGINAL_ID)
           ,OTHER_ID
    from   OTHER_ID_TABLE
    where  OTHER_ID in
    (
        Select distinct ID_1
        From   data1
    )
    group by OTHER_ID

)
select MIN_ORIGINAL_ID
      ,OTHER_ID
from cmedb.data2

现在我有两列数据，我想用它们来更新一个表.因此，我没有在底部使用 select，而是尝试了各种合并和更新的组合，包括在 UPDATE/MERGE 上方使用 WITH 语句，或者作为 UPDATE/MERGE 语句的一部分.以下是我心中最接近我想做的事情:

Now I have the two columns of data, I want to use them to update a table. So instead of having the select at the bottom, I've tried all sorts of combinations of merges and updates, including having the WITH statement above the UPDATE/MERGE, or as part of the UPDATE/MERGE statement. The following is what comes closest in my mind to what I want to do:

merge into ID_TABLE as it
using
(
    select MIN_ORIGINAL_ID
          ,OTHER_ID
    from cmedb.data2

) AS SEL
ON
(
    it.ID = sel.OTHER_ID
)
when matched then
update
set it.ORIGINAL_ID = sel.MIN_ORIGINAL_ID

所以它不起作用.我不确定这是否可行，因为我在互联网上没有找到将 WITH 语句与 UPDATE 或 MERGE 结合使用的示例.我有 WITH 语句与 INSERT 结合使用的示例，所以相信它是可能的.

So it doesn't work. I'm unsure if this is even possible, as I've found no examples on the internet using WITH statements in combination with UPDATE or MERGE. I have examples of WITH statements being used in conjunction with INSERT, so believe it might be possible.

如果有人可以提供帮助，那就太好了，如果我遗漏了任何有助于解决问题的信息，请告诉我.

If anyone can help it would be great, and please let me know if I've left out any information that would be useful to solve the problem.

免责声明:我提供的示例是我正在尝试做的简化版本，实际上可能没有任何意义！

Disclaimer: The example I've provided is a boiled down version of what I'm trying to do, and may not actually make any sense!

推荐答案

正如@Andrew White 所说，您不能在 MERGE 语句中使用公用表表达式.

As @Andrew White says, you can't use a common table expression in a MERGE statement.

但是，您可以使用嵌套子选择消除公用表表达式.这是您的示例选择语句，使用嵌套子选择重写:

However, you can eliminate the common table expressions with nested subselects. Here is your example select statement, rewritten using nested subselects:

select min_original_id, other_id
from (
   select min(original_id), other_id
   from   other_id_table
   where  other_id in (
      select distinct id_1 from (select id from id_table where id > 10) AS DATA1 (ID_1)
   )
   group by other_id
) AS T (MIN_ORIGINAL_ID, OTHER_ID);

这有点令人费解(确切的陈述可以写得更好)，但我意识到你只是给出了一个简化的例子.

This is somewhat convoluted (the exact statement could be written better), but I realize that you were just giving a simplified example.

您可以使用嵌套子选择而不是公用表表达式来重写您的 MERGE 语句.这在语法上当然是可能的.

You may be able to rewrite your MERGE statement using nested subselects instead of common table expressions. It is certainly syntactically possible.

例如:

merge into other_id_table x
using (
   select min_original_id, other_id
   from (
      select min(original_id), other_id
      from   other_id_table
      where  other_id in (
         select distinct id_1 from (select id from id_table where id > 10) AS DATA1 (ID_1)
      )
      group by other_id
   ) AS T (MIN_ORIGINAL_ID, OTHER_ID)
) as y
on y.other_id = x.other_id
when matched 
   then update set other_id = y.min_original_id;

同样，这很令人费解，但它向您表明它至少是可能的.

Again, this is convoluted, but it shows you that it is at least possible.

这篇关于可以将 DB2 WITH 语句用作 UPDATE 或 MERGE 的一部分吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！