sql - SQL Server CTE 中 WHERE EXISTS 中的引用列？

Question

我在 SQL Server 中有一个带有 WHERE EXISTS 子句的 CTE，我正在尝试而不是使用连接。

WITH Work_Info AS
(
  SELECT 
      a.fName,
      a.lName, 
      a.workID,
      a.carID
  FROM tbl_info a
  WHERE EXISTS (SELECT b.workID
                FROM tbl_work b
                WHERE a.workID = b.workID)
    AND EXISTS (SELECT c.carID, dbo.age_function(a.startDate, c.DOB) AS AGE
                FROM tbl_car c
                WHERE a.carID = c.carID
)

我正在尝试在 CTE 之后的语句中使用/引用别名为 AGE 的列，例如:

update tble_info a
SET adult =
   CASE WHEN AGE < 18 THEN 'NO'

Answer 1

我认为你误解了如何EXISTS作品。您不能从 EXISTS 子查询返回值。您需要使用 JOIN:

您可以按如下方式进行更新:

WITH Work_Info AS
(   SELECT  a.fName,
            a.lName, 
            a.workID,
            a.carID
            Age = dbo.age_function(a.startDate, c.DOB)
    FROM    tbl_info a
            INNER JOIN tbl_car c
                ON c.CarID = a.CarID
  WHERE     EXISTS
            (   SELECT  1
                FROM    tbl_work b
                WHERE   a.workID = b.workID
            )
)
UPDATE  Work_Info
SET     adult = CASE WHEN AGE < 18 THEN 'NO' ELSE 'YES' END;

但是，我建议不要使用这种方法(它甚至可能无法用作可更新的 CTE，我还没有测试过)。我会改为使用 MERGE(我不得不假设存在 ID 列作为 tbl_info 中的 PK):

WITH Work_Info AS
(   SELECT  a.ID,
            Age = dbo.age_function(a.startDate, c.DOB)
    FROM    tbl_info a
            INNER JOIN tbl_car c
                ON c.CarID = a.CarID
  WHERE     EXISTS
            (   SELECT  1
                FROM    tbl_work b
                WHERE   a.workID = b.workID
            )
)
MERGE tbl_Info a
USING Work_Info w
    ON a.ID = w.ID
WHEN MATCHED THEN UPDATE
    SET adult = CASE WHEN w,Age < 18 THEN 'NO' ELSE 'YES' END;

我更喜欢(并建议)使用 MERGE 的原因，即使您并没有真正合并，您只是在更新，在 sqlblog.com 上进行了讨论。

---

附录

为了进一步说明我更喜欢 MERGE 而不是仅使用 UPDATE 的主要原因是它可以防止非确定性更新。使用以下示例架构:

CREATE TABLE T1 (ID INT, Adult CHAR(3));
CREATE TABLE T2 (ID INT, Age INT);

INSERT T1 (ID) VALUES (1);
INSERT T2 (ID, Age) VALUES (1, 5), (1, 20);

我们可以看到 T1 中的单个 ID 在 T2 中有两行，一行超过 18 岁，另一行低于 18 岁，因此如果我们运行此更新:

WITH CTE AS
(   SELECT  T1.ID, T1.Adult, T2.Age
    FROM    T1
            INNER JOIN T2
                ON T1.ID = T2.ID
)
UPDATE  CTE
SET     Adult = CASE WHEN Age < 18 THEN 'NO' ELSE 'YES' END;

不会有错误，T1 会被更新，但我们不知道它会被设置为NO 还是YES，因为我们不知道当我运行上面的 Adult 设置为 NO 时，我不知道 T2 的哪个 reford 将是最后遇到的并且“坚持”在 T1 中，但是当我将插入更改为:

INSERT T2 (ID, Age) VALUES (1, 20), (1, 5); -- order changed

T1.Adult 设置为 YES。当我使用 MERGE 运行时:

WITH CTE AS
(   SELECT  T1.ID, T1.Adult, T2.Age
    FROM    T1
            INNER JOIN T2
                ON T1.ID = T2.ID
)
MERGE T1
USING CTE
    ON CTE.ID = #T1.ID
WHEN MATCHED THEN UPDATE 
    SET Adult = CASE WHEN Age < 18 THEN 'NO' ELSE 'YES' END;

我收到以下错误:

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

提示我检查我的查询，并确保它是确定性的。

链接文章中提到了其他原因，例如 ANSI 合规性(对我来说不是一个大问题)，以及使用 UPDATE .. FROM 和 INSTEAD OF 更新 View View 上的 TRIGGER 会失败，但 MERGE 不会，这又是我还没有睡过的事情。

不要误会我的意思，我仍然偶尔使用 UPDATE .. FROM，但只有当我确定重复不是问题时才真正用于大型一次性更新，因为它确实提供了以较低的 IO 进行更好的计划。