SQL Server USING Common Table Expression and perform multiple update commands

goucqfw6  于 2023-04-04  发布在  其他
关注(0)|答案(3)|浏览(80)

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.

nbysray5

nbysray51#

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.

vd2z7a6w

vd2z7a6w2#

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.

np8igboo

np8igboo3#

You May want to use a temporary table to save the result of your CTE and then update

IF OBJECT_ID(N'tempdb..#temp_cte') IS NOT NULL
BEGIN
  DROP TABLE #temp_cte
END

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

SELECT *
INTO #temp_cte
FROM Query

UPDATE
    Query.FOO = 1

UPDATE
    Query.BAR = 2;

相关问题