Add constant updatable column for a view in SQL Server

Question

I have this view :

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

GO 

Update View1 Set F3=1

These commands raise this error :

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

Is there any solution for updating "F3" field?

Thank you

Actually I Don't have permission to add a column to referenced Table. — Zohreh Tavakoli, May 18 '15 at 06:06
Why you want to UPDATE a VIEW? I think you must change your way and make a new view or use that view in another way ;). — shA.t, May 18 '15 at 06:11
It is perfectly legal to update through a view, but you're not updating the view, you're updating the underlying tables. — Lasse V. Karlsen, May 18 '15 at 06:14
@ZohrehTavakoli: I have updated my answer below to include a solution that allows you to UPDATE directly on the view, but you need an extra table to store the value of the F3 column. — Dan, May 18 '15 at 06:23

score 5 · Answer 1 · edited May 23 '17 at 11:51

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

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

Now, you could be tempted to put this code into a Stored Procedure. Unfortunately, this is not possible either, since DDL statements are not allowed in Stored Procedures. Another solution is therefore to store the value of column F3 in a separate table and change the view definition to bring 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 that your F3Column-table always contains exactly 1 record. If it doesn't contain any records, View1 will always be empty. If it contains more than 1 record, View1 will give you duplicates.

score 1 · Answer 2 · answered May 18 '15 at 06:06

1

F3 is not a column in any table, that's why you cannot update the value. Either you need to add it to your table or just display it as a new value in some conditions using CASE

CASE WHEN [condition goes here] THEN 1 END

answered May 18 '15 at 06:06

sqluser

5,502
7
36
50

Actually I need to make a new physical column on view. Is it possible? – Zohreh Tavakoli May 18 '15 at 06:15
1

No, such columns has to live in a table. – Lasse V. Karlsen May 18 '15 at 06:15
As @LasseV.Karlsen mentioned you cannot. But why do you want to update a non existed column? As I said in my answer you can just display it as another value in certain conditions – sqluser May 18 '15 at 06:25

shA.t · Answer 3 · 2015-05-18T06:12:51.857

0

I think you want a result that shows 1 as F3 in your results like this:

SELECT F1, F2, 1 As F3
FROM View1

Or make a new view like this:

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

edited May 18 '15 at 06:12

answered May 18 '15 at 06:06

shA.t

16,580
5
54
111

Add constant updatable column for a view in SQL Server

3 Answers3