Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    5

    Unanswered: Update or insert of view or function 'V1' failed because it contains a derived or con

    All,

    I don't understand why I'm not able to update a view.
    Here are the DDL:

    CREATE TABLE T1 ( key1 int, col2 varchar(10), primary key(key1) )
    CREATE TABLE T2 ( key1 int, col2 varchar(10), primary key(key1) )
    GO
    CREATE VIEW V1 AS
    SELECT * FROM T1 WHERE key1 = 0
    UNION ALL
    SELECT * FROM T2 WHERE key1 = 1

    UPDATE V1 SET col2 = 0 where KEY1 = 1

    and I get the message
    Msg 4406, Level 16, State 1, Line 1
    Update or insert of view or function 'V1' failed because it contains a derived or constant field.

    Does anyone have an idea ?
    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    See the remarks.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2002
    Posts
    5

    still don't understand...

    Hi,

    Thanks for your answer but I could'nt find the restriction that applies to my case...

    Which one does cause my failure ?

    Regards

  4. #4
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    Look under Updatable Views

    "only one base table."

    Tim S.

  5. #5
    Join Date
    Jul 2002
    Posts
    5

    Only one table is updated

    Hi,

    But, in this case, only one table can be updated.
    This is guaranted by the WHERE clause in the VIEW definition.
    I used the same criteria in my UPDATE query...

    However, I think that I will look for another way to do the things...
    Either INSTEAD OF TRIGGERS or some extra Programmation.

    Thanks to you
    Patrick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •