sql - 在SQL中使用Union执行更新

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上使用联合将这两个更新结合在一起？

使用Microsoft SQL Server Management Studio

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