Can a DB2 WITH statement be used as part of an UPDATE or MERGE?

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 hav开发者_JAVA技巧e the correct data values, I need to update the table.

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

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

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!

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.

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

For example:

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.

A way to use WITH statement with UPDATE (and INSERT too) is using SELECT FROM UPDATE statement (here):

WITH TEMP_TABLE AS (
    SELECT [...]
)
SELECT * FROM FINAL TABLE (
    UPDATE TABLE_A SET (COL1, COL2) = (SELECT [...] FROM TEMP_TABLE)
    WHERE [...]
);

I'm looking up the grammar now but I am pretty sure the answer is no. At least not in the version of DB2 I last used. Take a peek at the update and merge doc pages for their syntax. Even if you see the fullselect in the syntax you can't use with as that is explicitly separate according to the select doc page.

If you're running DB2 V8 or later, there's an interesting SQL hack here that allows you to UPDATE/INSERT in a query with a WITH statement. For inserts & updates that require a lot of preliminary data prepping, I find this method offers a lot of clarity.

Edit One correction here - selecting from UPDATE statements was introduced in V9 i believe, so the above will work for inserts on V8 or greater, and updates for V9 or greater.

Put the CTEs into a view, and select from the view in the merge. You get a clean, readable view that way, and a clean, readable merge.

Another method is to simply substitute your WITH queries and just use subselects.

For example, if you had (and I tried to include a somewhat complex example with some WHERE logic, an aggregate function (MAX) and a GROUP BY, just to show it more real world):

WITH
  Q1 AS (
    SELECT
      A.X,
      A.Y,
      A.Z,
      MAX(A.W) AS W
    FROM
      TABLEB B
      INNER JOIN TABLEA A ON B.X = A.X AND B.Y = A.Y AND B.Z = A.Z
    WHERE A.W <= DATE('2013-01-01') 
    GROUP BY
      A.X,
      A.Y,
      A.Z
  ),

  Q2 AS (
    SELECT
      A.X,
      A.Y,
      A.Z,
      A.W,
      MAX(A.V) AS V
    FROM
      Q1
      INNER JOIN TABLEA A ON Q1.X = A.X AND Q1.Y = A.Y AND Q1.Z = A.Z AND Q1.W = A.W
    GROUP BY
      A.X,
      A.Y,
      A.Z,
      A.W
  )

SELECT
  B.U,
  A.T
FROM
  Q2
  INNER JOIN TABLEA A ON Q2.X = A.X AND Q2.Y = A.Y AND Q2.Z = A.Z AND Q2.W = A.W AND Q2.V = A.V)
  RIGHT OUTER JOIN TABLEB B ON Q2.X = B.X AND Q2.Y = B.Y AND Q2.Z = B.Z

... you could turn this into something appropriate for a MERGE INTO by doing the following:

1. remove the WITH at the top
2. remove the comma from the end of the Q1 block (after the closing parenthesis)
3. take the Q1 AS from before the opening parenthesis and put is after the ending parenthesis (remove the comma) and then put the AS in front of the Q1.
4. take this new Q1 block and cut it and paste it into the Q2 block after the FROM Q1 (replacing the Q1 with the query in your clipboard) NOTE: leave the other references to Q1 (in the inner join keys) alone, of course.
5. Now you have a bigger Q2 query. Do steps 3 and 4 again, this time replacing the Q2 (after the FROM) in your main select with the bigger Q2 query in your clipboard.

In the end, you'll have a straight SELECT query that looks like this (reformatted to show proper indentation):

SELECT
  B.U,
  A.T
FROM
  (SELECT
     A.X,
     A.Y,
     A.Z,
     A.W,
     MAX(A.V) AS V
   FROM
     (SELECT
        A.X,
        A.Y,
        A.Z,
        MAX(A.W) AS W
      FROM
        TABLEB B
        INNER JOIN TABLEA A ON B.X = A.X AND B.Y = A.Y AND B.Z = A.Z
      WHERE A.W <= DATE('2013-01-01') 
      GROUP BY
        A.X,
        A.Y,
        A.Z) AS Q1
     INNER JOIN TABLEA A ON Q1.X = A.X AND Q1.Y = A.Y AND Q1.Z = A.Z AND Q1.W = A.W
     GROUP BY
       A.X,
       A.Y,
       A.Z,
       A.W) AS Q2
INNER JOIN TABLEA A ON Q2.X = A.X AND Q2.Y = A.Y AND Q2.Z = A.Z AND Q2.W = A.W AND Q2.V = A.V
RIGHT OUTER JOIN TABLEB B ON Q2.X = B.X AND Q2.Y = B.Y AND Q2.Z = B.Z

I have done this in my own personal experience (just now actually) and it works perfectly.

Good luck.