在SQL中使用Union执行UPDATE

如果我有这三个表(只是一个例子来学习UNION,这些不是真正的表)：

表格及其列：

Customer: 
id | name        | order_status
 
Order_Web:
id | customer_id | order_filled
 
Order:
id | customer_id | order_filled

我想在Order_Web表或使用Union的客户的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和订单上使用Union来组合这两个更新？

使用Microsoft sql Server Management Studio

解决方法

你不需要UINION – 用一对外连接替换内连接应该这样做：

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

在SQL中使用Union执行UPDATE

解决方法

猜你在找的MsSQL相关文章