kami kami - 7 months ago 18
SQL Question

Solved - USING Common Table Expression and perform multiple update commands

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

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.