How to add a column to select statement inside CTE to add value later

Question

I want to add a dummy column in CTE.

Later I want to update the value of dummy column using update statement.

I am getting Update or Insert of view or function failed because it contains a derived or constant field.

The CTE is

with CTE
AS
(
Select A.a, cast(NULL as varchar(20)) as F // cast expression is failed attempt to add dummy column.
FROM ABC A
)

I am getting exception after updating F field using update statement.

i just don't get your idea about adding a column. why do you need to do it? what is the problem of doing this expression without casting? `, '' AS F`? — John Woo, May 24 '13 at 09:27
`'' as F` was the first thing i tried, and got the same error. Cast i tried later, following this http://stackoverflow.com/questions/7916759/sql-how-to-add-a-column-in-the-select-query-result — Tilak, May 24 '13 at 09:28

score 0 · Answer 1 · answered May 24 '13 at 09:31

0

A CTE is basically a convenience to avoid having to use either

a) the same inline sql multiple times or b) a temporary table

If you want to add a column to this set, you can do it by joining your CTE to whatever it is you want to add.

answered May 24 '13 at 09:31

davek

1 Answers1