SQL Server : update column in a tree

Question

I want to update a column in a tree. I came up with the following statement :

WITH q AS (
    SELECT t1.*
    FROM buss_item t1
    WHERE t1.id_item = 218

    UNION ALL

    SELECT t2.*
    FROM buss_item t2
    JOIN q ON t2.parent_id = q.id_item
)
UPDATE q
SET default_item = 0

but I get an error:

Derived table 'q' is not updatable because a column of the derived table is derived or constant.

Any idea on how to fix this update ?

Answer 1

WITH q AS (
    SELECT t1.*
    FROM buss_item t1
    WHERE t1.id_item = 218

    UNION ALL

    SELECT t2.*
    FROM buss_item t2
    JOIN q ON t2.parent_id = q.id_item
)
UPDATE buss_item  set default_item = 0 from q
where q.item_ID=buss_item.ItemID

Answer 2

You cannot update CTE, you probably need to update the buss_item table according to the results of a CTE, try this:

WITH q AS (
    SELECT t1.*
    FROM buss_item t1
    WHERE t1.id_item = 218

    UNION ALL

    SELECT t2.*
    FROM buss_item t2
    JOIN q ON t2.parent_id = q.id_item
)

UPDATE  bi
SET     default_item = 0
FROM    buss_item bi
JOIN    q ON q.id_item = bi.id_item

Answer 3

You can update CTE, and this is very useful advanced feature of T-SQL. In fact you are not updating the CTE of course, but the tables included in its definition and you are doing it all in one step, I was amazed when I discovered this :) !! Same can be done with classical derived tables. You can even do several nests and still do updates of the real tables included in the first level of the definition. You can also use additional logic in between the nest levels, like using of ranking functions.

The error reported here is because of the UNION statement, is not allowed when doing this kind of operation, without it the update will succeed.