SQL Server Performance

Derived table is not updatable ?

Discussion in 'General Developer Questions' started by Bredsox, Feb 14, 2006.

  1. Bredsox New Member

    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
  2. Tahsin New Member

    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?
  3. Bredsox New Member

    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
  4. Adriaan New Member

    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.
  5. Tahsin New Member

    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

  6. Adriaan New Member

    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.
  7. Adriaan New Member

    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!
  8. Tahsin New Member

    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?
  9. Tahsin New Member

    He he, you are not getting that error, because now you included the # in UPDATE #T [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />It's even worse when you mismatch the table name/alias between the UPDATE clause and the FROM clause:<br /><br />CREATE TABLE #T (COL1 VARCHAR(10) PRIMARY KEY, COL2 VARCHAR(10))<br />INSERT INTO #T VALUES ('A', '')<br />INSERT INTO #T VALUES ('B', '')<br />INSERT INTO #T VALUES ('C', '')<br />UPDATE <font color="red">#T</font id="red"> SET Col1 = 'Z' FROM (SELECT * FROM #T WHERE COL1 = 'A') AS T<br />DROP TABLE #T<br /><br />Violation of PRIMARY KEY constraint 'PK__#T__3E52440B'. Cannot insert duplicate key in object '#T__________________________________________________________________________________________________________________000000000012'.<br />The statement has been terminated.<br /><br /><br /><br />Also note that there is no longer an error about updating the derived table!<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  10. Adriaan New Member

    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 ...
  11. Tahsin New Member

    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.
  12. Adriaan New Member

    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.
  13. Bredsox New Member

    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
  14. Tahsin New Member

    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.
  15. Bredsox New Member

    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
  16. Adriaan New Member

    Any triggers firing for updates on that view or on the underlying tables?
  17. Tahsin New Member

    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.

Share This Page