Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    21

    Unanswered: Derived table not updatable

    I got an error as follows:
    Derived table 'A' is not updatable because a column of the derived table is derived or constant.
    when I tried to run this query:
    update A set MonthsUnbilled =99999888
    FROM (select MonthsUnbilled from dbo.vw_MasterView
    WHERE (RecordID =8377396)) A

    This is a simplified query in order to pinpoint the culprit. I know I don't need to use a derived table if the real query is this simple.

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It would appear that either the dbo.vw_MasterView.MonthsUnbilled column is either a constant, a computed column, or derived from one of them.

    What is the DDL for dbo.vw_MasterView? What is the DDL for the table that contains the column referenced in dbo.vw_MasterView.MonthsUnbilled ?

    -PatP

  3. #3
    Join Date
    Aug 2002
    Posts
    21
    Thank you Pat for your response.
    I can update dbo.vw_MasterView.MonthsUnbilled without using a derived table. In other words, the following query runs just fine.
    update dbo.vw_MasterView set MonthsUnbilled =99999888
    FROM dbo.vw_MasterView
    WHERE (RecordID =8377396)

    But if I use a derived table like this:
    update A set MonthsUnbilled =99999888
    FROM (select MonthsUnbilled from dbo.vw_MasterView
    WHERE (RecordID =8377396)) A

    I get that error.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is there either a PRIMARY KEY or a UNIQUE constraint on the table column that eventually populates dbo.vw_MasterView.RecordID ? I think that there needs to be a constraint of one of those two types to make the virtualized view (the derived table created from an existing view) updatable.

    -PatP

  5. #5
    Join Date
    Aug 2002
    Posts
    21
    Pat, you're right. RecordID is the primary key and an identity field. But is this the reason why I got the error?

Posting Permissions

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