Using table-valued function in update statement

问题:

I'm trying to execute the following update statement:

Update belege2  
Set Preis_Einh = x.Preis, Aktion=x.Aktion, PreisHerk = x.PreisHerk 
FROM dbo.GetPreis(belege2.prodkey, belege2.Anzahl) x
where belege2.Beleg_Nr = 13599

SQL Server displays the following errors:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "belege2.prodkey" could not be bound.

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "belege2.Anzahl" could not be bound.

Is this not supported? Is there a workaround?

回答1:

did u try this with alias..

Update b  
Set b.Preis_Einh = x.Preis, b.Aktion=x.Aktion, b.PreisHerk = x.PreisHerk 
FROM belege2 b cross apply dbo.GetPreis(b.prodkey, b.Anzahl) x 
where b.Beleg_Nr = 13599

used cross apply