title: Can a DB2 WITH statement be used as part of an UPDATE or MERGE?
body: <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>with data1
  (
      ID_1
  )
  as
  (
      Select ID
      from  ID_TABLE
      where ID &gt; 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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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!</p>