Performing an UPDATE with Union in SQL
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
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