当前位置: 动力学知识库 > 问答 > 编程问答 >

tsql - Updating a CTE table fail cause of derived or constant field

问题描述:

I'm using MS-SQL 2012

WITH C1

(

SELECT ID, 0 as Match, Field2, Count(*)

FROM TableX

GROUP BY ID, Fields2

)

UPDATE C1 SET Match = 1

WHERE ID = (SELECT MATCHING_ID FROM AnotherTable WHERE ID = C1.ID)

This TSQL statement gives me the following error:

Update or insert of view or function 'C1' failed because it contains a derived or constant field.

Ideally I would like to create a "fake field" named Match and set its default value to 0. Then with the update I would like to Update ONLY the records that have an existing entry on the "AnotherTable".

Any thoughts what am I doing wrong?

Thanks in advanced.

网友答案:

Try doing a Left Outer Join like

   SELECT x.ID, ISNULL(a.Matching_ID, 0) as Match, x.Field2, Count(*)
   FROM TableX x
         LEFT OUTER JOIN AnotherTable a on x.ID = a.ID
   GROUP BY x.ID, ISNULL(a.Matching_ID, 0), x.Fields2

without the need of a C1

网友答案:

If I am understanding correctly, the problem is that you are trying to update the CTE table. If you update the table directly you should be fine.

Does this modified version help?

SELECT t.ID
, CASE WHEN (EXISTS (SELECT MATCHING_ID FROM AnotherTable WHERE ID = t.ID)) THEN 1 ELSE 0 END
,t.Field2
,Count(*)
FROM TableX t
GROUP BY ID, Fields2
分享给朋友:
您可能感兴趣的文章:
随机阅读: