I'm using MS-SQL 2012
SELECT ID, 0 as Match, Field2, Count(*)
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