CODEWITHSUNDEEP
t-sql
SF Developer
SF Developer

Reputation: 5384

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.

Upvotes: 1

Views: 2316

Answers (2)

user2295317
user2295317

Reputation:

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

Upvotes: 1

Johnny
Johnny

Reputation: 601

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

Upvotes: 1

Related Questions