SQL Server Performance Forum – Threads Archive

Derived table is not updatable ?

Hi Everyone,
What is this error mean? I have SP that only selects, updates and deletes in one table(ContractCreditCard). Msg 4421, Level 16, State 1, Procedure spBOP_ContractCreditCard, Line 88
Derived table ‘ContractCreditCard’ is not updatable because a column of the derived table is derived or constant.
Thanks
Dan

The error could mean that the SP cannot find one or more columns derived from an arithmetic expression, an aggregate, a built-in function, or a constant. Can you post the spBOP_ContractCreditCard code?

Even if I executed the sp with @Mode = ‘S’, it will still show that error. I can’t figure it out why it shows that kind of error. Here is the code sample if anyone can shed some light on this: CREATE PROCEDURE dbo.spBOP_ContractCreditCard
@ContractID int
,@CardHolderName varchar(35)
,@CreditCardNo varchar(20)
,@CreditCardType char(1)
,@CreditCardExp smalldatetime
,@Country char(3)
,@LastUpdateBy varchar(15)
,@Mode char(1) AS
SET Xact_Abort on IF @Mode = ‘I’
BEGIN
INSERT INTO ContractCreditCard
(
ContractID,
CardHolderName,
CreditCardNo,
CreditCardType,
CreditCardExp,
Country,
LastUpdate,
LastUpdateBy
)
VALUES
(
@ContractID,
@CardHolderName,
@CreditCardNo,
@CreditCardType,
@CreditCardExp,
@Country,
getdate(),
@LastUpdateBy
)
END
IF @Mode = ‘U’
BEGIN
UPDATE ContractCreditCard
SET
ContractID = @ContractID
,CardHolderName = @CardHolderName
,CreditCardNo = @CreditCardNo
,CreditCardType = @CreditCardType
,CreditCardExp = @CreditCardExp
,Country = @Country
,LastUpdate = getdate()
,LastUpdateBy = @LastUpdateBy
WHERE
ContractID = @ContractID
END
IF @Mode = ‘A’
BEGIN
SELECT
ContractID,
CardHolderName,
CreditCardNo,
CreditCardType,
CreditCardExp,
Country,
LastUpdate,
LastUpdateBy
FROM ContractCreditCard
END
IF @Mode = ‘S’
BEGIN
SELECT
ContractID,
CardHolderName,
CreditCardNo,
CreditCardType,
CreditCardExp,
Country,
LastUpdate,
LastUpdateBy
FROM ContractCreditCard
WHERE
ContractID = @ContractID
END
IF @Mode = ‘D’
BEGIN
DELETE FROM ContractCreditCard
WHERE
ContractID = @ContractID
END Thanks
Dan

It is a weird message … I tried this script to literally update a derived table, and the same error is raised: CREATE TABLE #T (COL1 VARCHAR(10) PRIMARY KEY)
INSERT INTO #T VALUES (‘A’)
UPDATE T SET COL1 = ‘B’ FROM (SELECT * FROM #T) AS T
DROP TABLE #T Server: Msg 4421, Level 16, State 1, Line 5
Derived table ‘T’ is not updatable because a column of the derived table is derived or constant. Is ContractCreditCard a view, rather than a table? In either case, does any of the columns that you’re trying to update have a Formula? You don’t need to update Formula columns.

Adriaan, you are missing the # in "UPDATE T"

Originally posted by Adriaan It is a weird message … I tried this script to literally update a derived table, and the same error is raised: CREATE TABLE #T (COL1 VARCHAR(10) PRIMARY KEY)
INSERT INTO #T VALUES (‘A’)
UPDATE #T SET COL1 = ‘B’ FROM (SELECT * FROM #T) AS T
DROP TABLE #T

Tahsin, In an UPDATE query, when you include an alias for the target table in the FROM clause, you must use the alias in the UPDATE clause as well. In this case, the target table was a derived table with the alias T.

It’s even worse when you mismatch the table name/alias between the UPDATE clause and the FROM clause: CREATE TABLE #T (COL1 VARCHAR(10) PRIMARY KEY, COL2 VARCHAR(10))
INSERT INTO #T VALUES (‘A’, ”)
INSERT INTO #T VALUES (‘B’, ”)
INSERT INTO #T VALUES (‘C’, ”)
UPDATE #T SET Col1 = ‘Z’ FROM (SELECT * FROM #T WHERE COL1 = ‘A’) AS T
DROP TABLE #T Violation of PRIMARY KEY constraint ‘PK__#T__3E52440B’. Cannot insert duplicate key in object ‘#T__________________________________________________________________________________________________________________000000000012’.
The statement has been terminated. Also note that there is no longer an error about updating the derived table!

I think ContractCreditCard is a table because that’s what Bredsox says in his original post.
So, assuming this is a table, I created the following table: CREATE TABLE CONTRACTCREDITCARD(
ContractID int,
CardHolderName varchar(35),
CreditCardNo varchar(20),
CreditCardType char(1),
CreditCardExp smalldatetime,
Country char(3),
LastUpdate smalldatetime,
LastUpdateBy varchar(15))
go Then I created his SP and ran @Mode = ‘I’ once or twice, and switched it to ‘U’ and ‘S’ EXEC spBOP_ContractCreditCard @ContractID = 2, @CardHolderName = ‘Test’, @CreditCardNo = ‘1111’,
@CreditCardType = ‘X’, @CreditCardExp = ’01/01/2006′, @Country=’USA’,
@LastUpdateby = ‘PROCEDURETEST’, @Mode=’U’
It seems to run fine for me. I guess I would like to know the datatypes for the columns for CONTRACTCREDITCARD. Could you provide that Bredsox or better yet, the CREATE script?

He he, you are not getting that error, because now you included the # in UPDATE #T [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan It’s even worse when you mismatch the table name/alias between the UPDATE clause and the FROM clause: CREATE TABLE #T (COL1 VARCHAR(10) PRIMARY KEY, COL2 VARCHAR(10)) INSERT INTO #T VALUES (‘A’, ”) INSERT INTO #T VALUES (‘B’, ”) INSERT INTO #T VALUES (‘C’, ”) UPDATE #T SET Col1 = ‘Z’ FROM (SELECT * FROM #T WHERE COL1 = ‘A’) AS T DROP TABLE #T Violation of PRIMARY KEY constraint ‘PK__#T__3E52440B’. Cannot insert duplicate key in object ‘#T__________________________________________________________________________________________________________________000000000012’. The statement has been terminated. Also note that there is no longer an error about updating the derived table! <hr height="1" noshade id="quote"></blockquote id="quote">

Well, the point here is that the UPDATE statement will ignore the FROM clause altogether, because there is no match for the name of the target table/alias named in the UPDATE clause. This fails because of the PK violation:
UPDATE #T SET Col1 = ‘Z’ FROM (SELECT * FROM #T WHERE COL1 = ‘A’) AS T … but this will update all rows in #T:
UPDATE #T SET Col2 = ‘Z’ FROM (SELECT * FROM #T WHERE COL1 = ‘A’) AS T
IMHO, there should be a syntax error for the mismatch: Server: Msg 107, Level 16, State 2, Line 1
The column prefix ‘<xxx>’ does not match with a table name or alias name used in the query.
Also … funny how the UPDATE part gets executed without the FROM part, but there’s no "Incorrect syntax near the keyword ‘FROM’." error for the unused remainder …

Ah, good point! I knew it was not taking into account the FROM clause, but your clever generation of the PK violation proves that point. I agree with you in that some kind of error message should be displayed because it is rather misleading.

The whole handling of aliases in UPDATE statements is a bit spooky. I checked BOL, and it turns out that you have the option, when you have a FROM clause with an alias, to refer to either the table name or its alias in the UPDATE clause. UPDATE #T SET Col2 = ‘Z’ FROM #T AS T … is equivalent to … UPDATE T SET Col2 = ‘Z’ FROM #T AS T
If you use a self-join, you must make sure you update the right instance of the table: This …
UPDATE #T SET Col1 = ‘G’
FROM #T AS T, #T
WHERE #T.COL1 = ‘A’ AND T.COL1 = ‘B’
… updates the ‘A’ row to ‘G’. This …
UPDATE T SET Col1 = ‘G’
FROM #T AS T, #T
WHERE #T.COL1 = ‘A’ AND T.COL1 = ‘B’
… updates the ‘B’ row to ‘G’. It is predictable behaviour, but IMHO it is a bit careless to allow this in the syntax.

Adriaan,
I found out that the table referenced here was infact a view that was created for encryption purpose. The view have only Insert and Update but not delete.So when I commented out the delete mode from the sp, it worked. Now, just a curiosity if the view have delete too, should this work without commenting out the sp? Thanks everyone for your time. ~~Dan

As long as the permissions are set properly for the View, you shouldn’t have any problems deleting from the SP. One thing that still puzzles me is that if you were testing with the Mode set to ‘I’ or ‘U’, that should not have generated an error message because it would have never fallen into @Mode = ‘D’ clause.

Tahsin,
That’s a good point and I was puzzled by that too. When I recreated the same sp with individual mode, it worked but as soon as I combined the delete mode with others, it didn’t. Does this ‘SET Xact_Abort on’ on the sp has to do anything related to this problem? Need experts advice here. Thanks Dan

Any triggers firing for updates on that view or on the underlying tables?

How is your application calling the stored procedure and passing parameters? Can you provide the exact syntax for this? SET Xact_Abort ON only raises a run-time error and rolls back the transaction, so it shouldn’t affect you. My long shot guess here is that somehow your application is trying to delete an entry that has not yet been created.

]]>