Search code examples
sqlsql-serverview

Update values in a view that is a Union of two tables

I have two simple tables (no constraints, one primary key, no triggers, etc) hosted on SQL Server that have identical schema. I have to keep the tables separate for various reasons but I want to be able to union them together in a view for oversight. I want a member of staff to be able to edit the tables via this unioned view.

The GlobalID is the primary key of each table and it is also unique across the unioned view.

CREATE VIEW V_View AS 
SELECT
[GlobalID]
,[UserComment]
FROM
TABLE_A

UNION

SELECT
[GlobalID]
,[UserComment]
FROM
TABLE_B

I've tried editing the data in this view through a couple of applications (Code on Time and Microsoft Access) but I get the same error:

Update or insert of view or function V_View failed because it contains a derived or constant field

If I remove the union from the view so that only one or the other table are contributing, the edits work perfectly so I am confident that it is the UNION command that is preventing edits.

Is there any way that I can make such a view editable? Could I construct it in a different way so that the derived/constant error does not prevent editing?

Solution

  • As written the view is not updateable since SQL Server does not know which rows belong in which table.

    The concept you need is known as a partitioned view. To allow the view to be updatable each table requires a check constraint on its primary key defining which range of values are allowable - for example:

    create table t1 (id Int constraint t1chk check (id < 10)  primary key, other columns);

    See a working example Fiddle here.