使用CTE更新无法识别set子句中的列名 [英] Update with CTE does not recognize column names in the set clause

问题描述

我需要用另一个表中的数据更新一个现有表.我的CTE给我正确的结果，但是当我尝试更新CTE时SSMS抱怨

I need to update an existing table with data from another. My CTE is giving me correct result, but when I'm trying to update with the CTE SSMS complains on

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '.'. 

或以下行中的无效列名:

or Invalid column names at the lines below:

set cm.Action.Identifier_fk = ID
set cm.ActionRequestedAction = Action
set cm.Action.apartment_fk = apartment_fk

这是代码:

Use DB;
GO

with CTE (ID,Action,Identifier_fk,apartment_fk) AS 

(select a.ID, a.Action, b.Identifier_fk, m.apartment_fk 
from Project.AllSent a (nolock) 
    left outer join cm.Action b (nolock) on a.ID=b.Identifier_fk
    left Outer Join csv.Matching m (nolock) on m.Identifier_fk = a.ID
    left outer join csv.Apartment p (nolock) on m.apartment_fk=p.apartment_pk
    where b.Identifier_fk is NULL) 

update cm.Action 
set cm.Action.Identifier_fk = ID
set cm.Action.RequestedAction = Action
set cm.Action.apartment_fk = apartment_fk

    from CTE c
    JOIN Project.AllSent t (nolock) on t.ID=c.ID;

推荐答案

正确的 update 语句只有一个 set .另外， from 子句中没有 cm .我将建议取消CTE:

The correct update statement has only one set. Also, you have no cm in the from clause. I'm going to propose getting rid of the CTE:

update b
    set Identifier_fk = a.ID,
        Action.RequestedAction = a.Action,
        apartment_fk = mm.apartment_fk
    from Project.AllSent a (nolock) left join
         cm.Action b (nolock)
         on a.ID = b.Identifier_fk left join
         csv.Matching m (nolock)
         on m.Identifier_fk = a.ID left join
         csv.Apartment p (nolock)
         on m.apartment_fk = p.apartment_pk
    where b.Identifier_fk is NULL;

我认为不需要最后的 join .

这篇关于使用CTE更新无法识别set子句中的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！