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.
3条答案
按热度按时间nbysray51#
You can insert your
CTE
result to a@Table
variable and use this Table wherever required in the code block. (You canjoin
this Table with actual table to perform theUPDATE/INSERT/DELETE
etc). You can't use the same CTE in multiple statement, because CTE is part of the subsequent statement only.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. TheUPDATE
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
, andDELETE
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.
np8igboo3#
You May want to use a temporary table to save the result of your CTE and then update