CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expression
Raj
Raj

Reputation: 10853

Update CTE updates the underlying table

DB Platform - MS SQL Server 2008R2 I have this table - 

CREATE TABLE [dbo].[CaseTest](
    [SampleText] [varchar](5) NULL,
    [ID] [int] NULL,
    [Name] [nchar](10) NULL
)

GO

Populated with sample data

INSERT INTO CaseTest
VALUES ('Text1',1,NULL),
('Text2',2,NULL),
('Text3',3,NULL),
('Text4',4,NULL),
('Text5',5,NULL)

Now when I run the following query, I really did not expect any changes to the data, but I find that the actual table data got updated.

WITH CTE AS(
SELECT * FROM casetest
)
UPDATE CTE SET Name = 'NameText'

Is this the expected behaviour? How does this actually happen?

Upvotes: 1

Views: 1495

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

As you yourself observe in a comment:

I assumed that this would be meaningless

And, indeed, it would be for any of the statements that actually make changes to the database - every such operation could be optimized away as a no-op. And so, it wouldn't actually make sense in such a case to even allow you to write something other than a SELECT after creating CTEs.

But that's not the route they chose to go down - they do allow UPDATE, INSERT, DELETE, etc to be written. By the same logic that they allow certain updating operations to be applied to views. Provided that the database engine can take your UPDATE statement and, via the CTE, can determine a single target table to UPDATE, the UPDATE via the CTE will be allowed.

since the CTE is just an in-memory object

It's not. It's just a named query, nothing is materialized when the CTE is written. Effectively, the CTE is inserted into the final query that uses it (in as many places as it's referenced) and then the entire query is optimized and executed.

The database engine may decide, at the point of optimizing the final query, to materialize some or all of the CTE into storage (via it's Spool operators) but that's an optimization decision that occurs later - and one that the engine is equally capable of deciding to use even for simple, non-CTE queries.

Upvotes: 3

Related Questions