Tuesday, March 27, 2012

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!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|||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.|||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|||Pat, you're right. RecordID is the primary key and an identity field. But is this the reason why I got the error?

No comments:

Post a Comment