title: Using MERGE in SQL Server 2014
body: <p>I have a problem using <code>MERGE</code> in SQL Server.</p>
  
  <pre><code>MERGE Info_Game AS t 
  USING (
  with cte_example 
  as
  (SELECT ig_idx,ig_team1,ig_team2benefit,ig_game_type
  FROM (select ig_idx,ig_team1,ig_team2benefit,ig_game_type,
  RANK() OVER(partition by ig_root,ig_game_type order by ig_idx asc) AS rank
  FROM  info_game ) AS t1
  WHERE rank &lt; 2)
  
  select *
         ,count(name) over(partition by name) count
  from cte_example
  
  ) s ON t.ig_outcome_id = s.ig_idx 
  
  WHEN MATCHED THEN
      UPDATE 
          SET s.IG_Team1Benefit = t.pv_v1,
              s.ig_drawbenefit = t.pv_v2,
              s.IG_Team2Benefit = t.pv_v3
  
  WHEN NOT MATCHED THEN
      INSERT (ig_idx, ig_team1, ig_team2benefit, ig_game_type) 
      VALUES (s.ig_idx, s.ig_team1, s.ig_team2benefit, s.ig_game_type)
  OUTPUT $action, Inserted.*, Deleted.*;
  </code></pre>
  
  <p>but I get an error in WITH cte_example</p>
  
  <blockquote>
    <p>Msg 156, Level 15  </p>
  </blockquote>
  
  <p>How can I do this?</p>