可以将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

现在我有两列数据，我想用它们来更新表.因此，我没有尝试在底部进行选择，而是尝试了各种合并和更新的组合，包括将WITH语句置于UPDATE/MERGE上方，或者作为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.

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

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屋！