Post reply

CTE update column

  • October 24, 2014 at 4:18 am

    #294423

    With cte_table (columna)

    As (select a from cust)

    Update columna

    Set a = 'hello'

    Question: would a in cust be updated even though the update is to the CTE column named columna? Or would there be an error where the column name must match?

  • October 24, 2014 at 4:21 am

    #1754587

    You'll get an error. You reference the column name where the table name needs to be in the update and you reference a column which doesn't exist in that scope

    This, however, will work

    With cte_table (columna)

    As (select a from cust)

    Update cte_table

    Set columna = 'hello'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 24, 2014 at 7:59 am

    #1754676

    Gail fixed your code, but I'll explain a little:

    A CTE is really nothing more than a temporary view. Because of that, it acts just like a view when you interact with it. This means you can SELECT, INSERT, UPDATE, and DELETE against the CTE (with some oddities.)

    If you UPDATE the CTE, it will work as long as you don't attempt to UPDATE multiple underlying tables. If you do, you'll get a very nice error message telling you exactly what you did wrong. (Coincidentally, it's one of my favorite error messages for this reason.) You'll get the same thing if you attempt to INSERT into the CTE and define columns from multiple tables. You can't DELETE from a multi-table CTE at all (same error message.)

    Msg 4405, Level 16, State 1, Line 1

    View or function 'dbo.JK_Test_View_2' is not updatable because the modification affects multiple base tables.

    You CAN, however, do any of the above against a CTE if it includes derived columns (COUNT, SUM, ROW_NUMBER, etc) from a single table. This makes a CTE very nice for deduplicating data - you can use ROW_NUMBER to look for rows with duplicate keys, and then DELETE where the ROW_NUMBER > 1. This gets around the inability to use window functions such as ROW_NUMBER() in the where clause.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply