Clever Geek Handbook

Add persistent updatable column for view in SQL Server

I have this view:

Create View View1 
AS 
Select F1,F2,0 As F3
From TB1

GO 

Update View1 Set F3=1

These commands cause this error:

Updating or inserting view or function 'view1' failed because it contains a derived or constant field.

Is there any solution to update the "F3" field?

thank

+3


source to share


3 answers


The only way to change the value of a constant column in a view is to change the view itself. It doesn't make sense to run UPDATE statements on a constant or computed column on a view, as this value is not physically stored in any table. Therefore, you need to do something like this:

ALTER VIEW View1 A
Select F1,F2,1 As F3
From TB1

Now, you might be tempted to include this code in your stored procedure. Unfortunately, this is not possible because DDL statements are not allowed in stored procedures . So another solution is to store the F3 column value in a separate table and change the view definition to cast the value into the view:



CREATE TABLE F3Column (
    Value AS int
)

go

INSERT F3Column VALUES (0)

go

CREATE VIEW View1 AS
select F1, F2, Value AS F3
from TB1, F3Column

go

-- This will allow you to change the value directly on the view:
UPDATE View1 SET F3 = 1

Just make sure the F3Column table always contains exactly 1 record. If it contains no records, View1 will always be empty. If it contains more than 1 record, View1 will give you duplicates.

+4


source


I think you want a result that shows 1

how F3

in your results:

SELECT F1, F2, 1 As F3
FROM View1


Or create a new view like this:

Create View View2 
AS 
Select F1, F2, 1 As F3
From TB1    
GO
0


source


F3

is not a column in any table, so you cannot update the value. Either you need to add it to the table, or just display it as a new value under some conditions usingCASE 

CASE WHEN [condition goes here] THEN 1 END
0


source






More articles:


All Articles