USING Common Table Expression and perform multiple update commands

Question

is it possible to us a CTE to perform multiple update commands?

With Query AS
(
    SELECT
        Table_One.FOO AS FOO,
        Table_Two.BAR AS BAR
    FROM FOO
    JOIN BAR ON FOO.ID = BAR.ID
)
UPDATE
    Query.FOO = 1;
UPDATE
    Query.BAR = 2;

In the example Query isn't available anymore on the second UPDATE command.

EDIT:

My working code looks like this:

With Query AS
(
    SELECT
        Table_One.FOO AS FOO,
        Table_Two.BAR AS BAR
    FROM FOO
    JOIN BAR ON FOO.ID = BAR.ID
)
UPDATE
    Query.FOO = 1

With Query AS
(
    SELECT
        Table_One.FOO AS FOO,
        Table_Two.BAR AS BAR
    FROM FOO
    JOIN BAR ON FOO.ID = BAR.ID
)
UPDATE
    Query.BAR = 2;

Because you can't Update two Tables with one UPDATE command I need two Update commands. The problem right know is, that if I need to change the Select in the CTE I have to do it on two locations in the code.

Answer 1

A SQL Server UPDATE only allows you to update a single table. As buried in the documentation:

The following example updates rows in a table by specifying a view as the target object. The view definition references multiple tables, however, the UPDATE statement succeeds because it references columns from only one of the underlying tables. The UPDATE statement would fail if columns from both tables were specified.

Although views and CTEs are not exactly the same thing, they often follow similar rules. So, this is also explained in the section on updatable views:

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

You can effectively do what you want by issuing two updates and wrapping them in a single transaction.

Answer 2

You can insert your CTE result to a @Table variable and use this Table wherever required in the code block. (You can join this Table with actual table to perform the UPDATE/INSERT/DELETE etc). You can't use the same CTE in multiple statement, because CTE is part of the subsequent statement only.