Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I have been trying to understand what is wrong with the following view, and unfortunately I was not able to find my answer anywhere, other than using triggers, which I would like to avoid. Given the following view, when I try to insert into it I get the error above, however if I remove the inner join to the Company table everything seems to work just fine:

CREATE VIEW [dbo].[vwCheckBookingToCheck]
WITH SCHEMABINDING
AS
SELECT  [checkUser].[CheckID] ,
        [checkUser].[CheckToTypeID] ,
        [checkUser].[CheckNumber] ,
        [checkUser].[CheckDate] ,
        [checkUser].[CheckAmount] ,
        [checkUser].[CheckStatusID] ,
        [checkUser].[CheckAcceptedBy] ,
        [checkUser].[CreatedBy] ,
        [checkUser].[CreatedDateTime] ,
        [checkUser].[CheckToUserID] [ToID],
        [checkUser].[CheckFromCompanyID] [FromID],
        [companyFrom].[CompanyName]
FROM    [dbo].[CheckUser] [checkUser]
        INNER JOIN [dbo].[Company] [companyFrom] ON [companyFrom].[CompanyID] = [checkUser].[CheckFromCompanyID]

UNION ALL

SELECT  [checkCompany].[CheckID] ,
        [checkCompany].[CheckToTypeID] ,
        [checkCompany].[CheckNumber] ,
        [checkCompany].[CheckDate] ,
        [checkCompany].[CheckAmount] ,
        [checkCompany].[CheckStatusID] ,
        [checkCompany].[CheckAcceptedBy] ,
        [checkCompany].[CreatedBy] ,
        [checkCompany].[CreatedDateTime] ,
        [checkCompany].[CheckToCompanyID] [ToID],
        [checkCompany].[CheckFromCompanyID] [FromID] ,
        [companyFrom].[CompanyName]
FROM    [dbo].[CheckCompany] [checkCompany]
       INNER JOIN [dbo].[Company] [companyFrom] ON [companyFrom].[CompanyID] = [checkCompany].[CheckFromCompanyID]

GO

Here is my insert, I am only inserting in [CheckUser] or [CheckCompany]:

INSERT INTO [dbo].[vwCheckBookingToCheck]   
    ( [CheckToTypeID] ,
      [CheckNumber] ,
      [CheckDate] ,
      [CheckAmount] ,
      [CheckStatusID] ,
      [CheckAcceptedBy] ,
      [CreatedBy] ,
      [CreatedDateTime] ,
      [ToID] ,
      [FromID] 
    )
     SELECT 2,
      'Test' , -- CheckNumber - varchar(255)
      '2014-08-23 20:07:42' , -- CheckDate - date
      1233 , -- CheckAmount - money
      0 , -- CheckStatusID - int
      1 , -- CheckAcceptedBy - int
      1 , -- CreatedBy - int
      '2014-08-23 20:07:42' , -- CreatedDateTime - datetime
      1,  -- ToID - int
      1  -- FromID - int

CheckToTypeID is my check constraint, is there any way to make this view work with inner joins? Again, if I remove the inner joins I am able to get it to work, but I would like to keep them if possible.

I am using SQL Server 2012, any help is appreciated.

Thanks, Paul

like image 261
user3800174 Avatar asked Aug 24 '14 13:08

user3800174


People also ask

How can we update a view in SQL?

To modify table data through a view. In Object Explorer, expand the database that contains the view and then expand Views. Right-click the view and select Edit Top 200 Rows. You may need to modify the SELECT statement in the SQL pane to return the rows to be modified.

Can we perform insert or update on view?

We cannot insert or update data using view. The view is a virtual table. We can do those action, but it's effect on real table data too. View is like a virtual table which enable us to get information of multiple tables.

Can you insert into a view in SQL?

You can insert data through a single-table view if you have the Insert privilege on the view. To do this, the defining SELECT statement can select from only one table, and it cannot contain any of the following components: DISTINCT keyword.

Can we update rows in view?

You can insert, update, and delete rows in a view, subject to the following limitations: If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows. You can't directly modify data in views based on union queries.


1 Answers

This is a bit long for a comment.

I cannot readily find the 2012 documentation on this subject, but the SQL Server 2008 documentation is quite clear:

A view is considered an updatable partitioned view when the view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each SELECT statement references one SQL Server base table.

You have two tables in the from clause, so it is not updatable. It is a read-only view. I am not aware that this was changed in 2012.

like image 99
Gordon Linoff Avatar answered Nov 15 '22 19:11

Gordon Linoff