在 SQL 中使用 Union 执行 UPDATE [英] Performing an UPDATE with Union in SQL

问题描述

如果我有这三个表(只是为了学习 UNION 的一个例子，这些不是真正的表):

If I were to have these three tables (just an example in order to learn UNION, these are not real tables):

表格及其列:

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 :

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

如何在 order_web 和 order 上使用联合来组合这两个更新?

How can I combine these two updates using a Union on order_web and order?

使用 Microsoft SQL Server Management Studio

Using Microsoft SQL Server Management Studio

推荐答案

为此您不需要 UNION - 用一对外部连接替换内部连接应该这样做:

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

你也可以使用WHERE EXISTS，像这样:

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
)

如果你必须使用UNION，你可以这样做:

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
)

或带有 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！