Using Output Clause with CTE throws mult-part identifier could not be bound error

86 views Asked by At

I am trying to use Output clause with update statement(using CTE). But I repeatedly receive multi-part identifier could not be bound error.

;With CTE_Duplicates as
(  SELECT
    t1.[Id] as ID1, t2.[Id] as ID2
FROM
    (
    SELECT 
      Id, Value,TypeID, DatabaseID
    FROM
       TypeValue
       where DatabaseID in (1)   
    ) T1
   JOIN
   (
    SELECT 
     Id,Value,TypeID, DatabaseID
    FROM
       TypeValue
       where DatabaseID in (2)   
    ) T2 on
    T1.Value = T2.Value AND T1.dTypeID = T2.TypeID and T2.DatabaseID in (1,2)
    )
update Scenario set Scenario .[Symbol] = CTE_Duplicates.[ID1]
    output  inserted.Symbol, deleted.Symbol, Scenario.ID
    into Log(newid,oldid, TableID)
    from  CTE_Duplicates INNER JOIN Scenario ON CTE_Duplicates.ID1=Scenario.Symbol
    where Scenario.[Symbol]= CTE_Duplicates.[ID2]

Error: Msg 4104, Level 16, State 1, Line 25

The multi-part identifier "Scenario.ID" could not be bound.

0

There are 0 answers