如果我有这三个表(只是一个例子来学习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来组合这两个更新?
解决方法
你不需要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