Using MERGE in SQL Server 2014

I have a problem using MERGE in SQL Server.

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 < 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.*;

but I get an error in WITH cte_example

Msg 156, Level 15

How can I do this?

2 answers

  • answered 2017-10-11 10:18 Tapakah Ua

    ;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 < 2)
    MERGE 
    Info_Game AS t 
    USING (
    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)
    

  • answered 2017-10-11 10:20 AB_87

    Cte should not be inside Merge statement. Move it out.

    ;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 < 2)
    
    MERGE 
    Info_Game AS t 
    USING (
    
    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.*;