在 SQL 中使用 Union 执行 UPDATE

Question

如果我有这三个表（只是为了学习 UNION 的一个例子，这些不是真正的表）：

表格及其列：

 Customer:
id | name        | order_status

Order_Web:
id | customer_id | order_filled

Order:
id | customer_id | order_filled

当使用 Union 为该客户填写 Order_Web 表或 Order 表时，我想更新 Customer 表中的 order_status：

 UPDATE c
SET c.order_status = 1
FROM Customer AS c
INNER JOIN Order_Web As ow
      ON c.id = ow.customer_id
WHERE ow.order_filled = 1

UPDATE c
SET c.order_status = 1
FROM Customer AS c
INNER JOIN Order As o
      ON c.id = o.customer_id
WHERE o.order_filled = 1

如何使用 order_web 和 order 上的 Union 组合这两个更新？

使用 Microsoft SQL Server Management Studio

原文由 Jaiesh_bhai 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您不需要 UNION 为此 - 用一对外部联接替换内部联接应该这样做：

 UPDATE c
SET c.order_status = 1
FROM Customer AS c
LEFT OUTER JOIN Order_Web As ow ON c.id = ow.customer_id
LEFT OUTER JOIN Order As o ON c.id = o.customer_id
WHERE ow.order_filled = 1 OR o.order_filled = 1

您也可以使用 WHERE EXISTS ，如下所示：

 UPDATE c
SET c.order_status = 1
FROM Customer AS c
WHERE EXISTS (
    SELECT 1 FROM Order_Web As ow WHERE c.id = ow.customer_id AND ow.order_filled = 1
) OR EXISTS (
    SELECT 1 FROM Order As o WHERE c.id = o.customer_id AND o.order_filled = 1
)

如果你必须使用 UNION ，你可以这样做：

 UPDATE c
SET c.order_status = 1
FROM Customer AS c
WHERE c.id in (
        SELECT ow.id FROM Order_Web As ow WHERE ow.order_filled = 1
    UNION
        SELECT o.id FROM Order As o WHERE o.order_filled = 1
)

或带有 JOIN 的同一个：

 UPDATE c
SET c.order_status = 1
FROM Customer AS c
JOIN (
        SELECT ow.id AS id FROM Order_Web As ow WHERE ow.order_filled = 1
    UNION
        SELECT o.id AS id FROM Order As o WHERE o.order_filled = 1
) AS ids ON ids.id = c.id

原文由 Sergey Kalinichenko 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

UPDATE  c
SET     c.order_status = 1
FROM    (
        SELECT  customer_id
        FROM    order_web
        WHERE   order_filled = 1
        UNION
        SELECT  customer_id
        FROM    order
        WHERE   order_filled = 1
        ) o
JOIN    customer c
ON      c.id = o.customer_id

原文由 Quassnoi 发布，翻译遵循 CC BY-SA 3.0 许可协议