CTE update column

Question

CTE update column

kevkevkev

Valued Member

Points: 61
More actions
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?

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

sqlslacker SSChasing Mays Points: 657 More actions · Answer 2

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.