CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008sql-server-2008-r2
Mar1009
Mar1009

Reputation: 811

Update a cte 'result' failed because it contains a derived or constant field

I have a cte like below;

;with cte as (field1, field2, field3)

I need to select the records from this CTE, but before I need to do some updates on one of the columns in that CTE and that update is based on a output from scalar function (it will return either 1 or 0 as output (bit)) which I use it in a case statement like below;

 ;with cte as (field1, field2, field3)
 update cte
 set field1 = (select 
                     case when dbo.scalarFunction (@parm1,@parm2) = 0
                        then 'New Value'
                        else cte.field1 
                     end)
 from cte
 where field2 = 'some filter' and field3 = 'some filter'

 select * from cte

When I execute this I get the below error;

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

Can anyone please advice how to update my cte and then then see the updated records in cte

Upvotes: 0

Views: 3938

Answers (1)

DatabaseCoder
DatabaseCoder

Reputation: 2032

Maybe you can do this changes in select instead of update command -

;with cte as (field1, field2, field3)
 select
    field1 = (case when field2 = 'some filter' and field3 = 'some filter' then
                (case when dbo.scalarFunction (@parm1,@parm2) = 0
                    then 'New Value'
                    else cte.field1 
                end)
            else field1 end)
    ,field2
    ,field3
 from cte

Upvotes: 1

Related Questions