sql - 使用公共(public)表表达式并执行多个更新命令

Question

我们是否可以使用 CTE 执行多个更新命令？

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;

在示例中，第二个 UPDATE 命令中的查询不再可用。

编辑:

我的工作代码如下所示:

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;

因为您无法使用一个 UPDATE 命令更新两个表，所以我需要两个 Update 命令。正确知道的问题是，如果我需要更改 CTE 中的选择，我必须在代码中的两个位置执行此操作。

Answer 1

SQL Server UPDATE 只允许您更新单个表。正如埋在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.

尽管 View 和 CTE 并不完全相同，但它们通常遵循相似的规则。因此，updatable views 部分也对此进行了解释。 :

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

您可以通过发出两个更新并将它们包装在单个事务中来有效地完成您想要的操作。