If I were to have these three tables (just an example in order to learn UNION, these are not real tables):
Tables with their columns:
Customer: id | name | order_status Order_Web: id | customer_id | order_filled Order: id | customer_id | order_filled
And I wanted to update order_status in the Customer table when there is a order filled in either the Order_Web table or the Order table for that customer using Union:
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
How can I combine these two updates using a Union on order_web and order?
Using Microsoft SQL Server Management Studio
Advertisement
Answer
You do not need a UNION
for that – replacing an inner join with a pair of outer ones should do it:
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
You could also use a WHERE EXISTS
, like this:
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 )
If you must use UNION
, you can do it as follows:
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 )
or the same one with a 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