UPDATE with WITH-AS clause

Question

When I run the following script, Original_Table gets updated by the UPDATE statement

WITH temp AS (
    SELECT 
      ROW_NUMBER() over (partition by x order by y) row_num, x, z
    FROM Original_Table )

UPDATE temp set z = a + (select ISNULL(SUM(a),0) from temp A where A.x= temp.xand A.row_num < temp.row_num)

But when I replace WITH with the following

CREATE TABLE
    #temp
(
    row_num INT NOT NULL ,
    x INT   NOT NULL,
    a DECIMAL   NOT NULL ,
    z DECIMAL    NULL 
);

insert into #temp
    SELECT 
      ROW_NUMBER() over (partition by x order by y) row_num, x, z
    FROM Original_Table 

UPDATE temp set z = a + (select ISNULL(SUM(a),0) from temp A where A.x= temp.xand A.row_num < temp.row_num)

the UPDATE updates only the #temp table but not Original_Table

Why is that?

This is known as common table expression. For MS Sql see https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx — Serg, Dec 06 '16 at 17:17

HABO · Accepted Answer · 2016-12-06T23:12:37.133

The first example updates temp which is based on a query from Original_Table by way of the CTE. The base table is updated when the CTE is updated. As explained here, the CTE is within the scope of the UPDATE statement.

The second example queries Original_Table and saves the result in a new table: #temp. The temporary table is then updated. There is no magic memory that ties the rows in #temp back to the rows in Original_Table, and you probably wouldn't want one.

score 1 · Answer 2 · answered Dec 06 '16 at 19:02

1

That very last statement

UPDATE temp
 set z = a + (select ISNULL(SUM(a),0)
               from temp A where A.x= temp.x
                and A.row_num < temp.row_num)

only updates some table name "temp". There is nothing in the statement that references Original_Table

answered Dec 06 '16 at 19:02

Philip Kelley

39,426
11
57
92

Yes it does, because Original_Table is referenced within the statement. Your first script is one single statement--a long and reasonably complex Common Table Expression that performs an update. The second script is three statements: a CREATE (temp) TABLE, followed by an INSERT, followed by an an UPDATE. – Philip Kelley Dec 06 '16 at 20:59

UPDATE with WITH-AS clause

2 Answers2