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.