title: SQL Server : using MERGE statement to update two tables
body: <p>I am trying to use a <code>MERGE</code> statement to update 2 tables using a single source (CTE). It works when I use only 1 merge, but when I add the 2nd one, it returns an error: </p>
  
  <blockquote>
    <p>Msg 208, Level 16, State 1, Procedure mn_SeoUrl_UpdateBulk, Line 46<br>
    Invalid object name 'cte'.</p>
  </blockquote>
  
  <p>Is it possible to update 2 tables with a merge? If it is possible, I am doing it incorrectly and I hope someone can show me what is the correct method of doing this. </p>
  
  <p>Any help is much appreciated. </p>
  
  <p>Thank you.</p>
  
  <p>Here is my failing code (false col names):</p>
  
  <pre><code>WITH cte AS 
  ( 
      SELECT
          [u].[col1], [u].[col2], [u].[col3], 
          CASE                                        
             WHEN [u].[col1] LIKE 'L%' 
                THEN 'c/' + [u].[col2] + '/' + [u].[col3]
             WHEN [u].[col1] LIKE 'M%' 
                THEN 'm/' + [u].[col2] + '/' + [u].[col3]
          END [col4]
      FROM
          (SELECT
               [st1].[col1], [st1].[col2], [st1].[col3]
           FROM
               [dbo].[sourcetable1] [st1]
           INNER JOIN 
               [dbo].[sourcetable2] [st2] ON [st1].[ID] = [st2].[ID]
           WHERE
               [pd].[col2] &lt;&gt; 0) [u]
  )
  MERGE [dbo].[table1] AS [Target]
  USING [cte] AS [Source] ON [Target].[ID] = [Source].[ID]
  
  WHEN MATCHED THEN
      UPDATE 
          SET [Target].[col] = [Source].[col]
  
  WHEN NOT MATCHED BY TARGET THEN
      INSERT ([col])
      VALUES ([Source].[col]);
  
  MERGE [dbo].[tabl2] AS [Target]
  USING [cte] AS [Source] ON [Target].[id] = [Source].[id]
  
  WHEN MATCHED THEN
      UPDATE 
          SET [Target].[col] = [Source].[col]
  
  WHEN NOT MATCHED BY TARGET THEN
      INSERT ([col])
      VALUES ([Source].[col]);
  END;
  </code></pre>