MySQL：更新表中与另一个查询结果匹配的所有行

Question

我编写了一个查询语句，返回关联客户和销售人员的行。

请注意，该查询涉及多个数据库表的连接。另外请注意，并非所有客户都有销售人员。

c_id     c_name   s_id   s_name
  24  microsoft      1     mike
  27        sun      1     mike
  42      apple      2     bill
  44     oracle      1     mike
  47        sgi      1     mike
  58       ebay      2     bill
  61     paypal      3      joe
  65     redhat      1     mike

我在数据库中有一张单独的表格（名为invoices），它看起来像这样。

i_id   c_id   c_name   s_id   s_name
7208     22   toyota   NULL     NULL
7209     23     ford   NULL     NULL
7210     27      sun   NULL     NULL
7211     42    apple   NULL     NULL
7212     12   nissan   NULL     NULL
7213     15       gm   NULL     NULL
7214     61   paypal   NULL     NULL

我该如何在MySQL中使用UPDATE语句来将我的发票表格更新为下面的表格？

i_id   c_id   c_name   s_id   s_name
7208     22   toyota   NULL     NULL
7209     23     ford   NULL     NULL
7210     27      sun      1     mike
7211     42    apple      2     bill
7212     12   nissan   NULL     NULL
7213     15       gm   NULL     NULL
7214     61   paypal      3      joe

也就是说，如果存在销售人员与客户的关系，我该如何更新发票表以包括正确的销售人员ID和姓名呢？如果有该客户的销售人员，那么该客户的所有发票都应该与销售人员相关联。

非常感谢 :-)

- Tommy O'Dell

Answer 1

使用子查询

最广泛支持的选项

UPDATE INVOICES
   SET s_id = (SELECT cs.s_id
                 FROM CUSTOMERS_AND_SALES cs
                WHERE cs.c_id = INVOICES.c_id),
       s_name = (SELECT cs.s_name
                   FROM CUSTOMERS_AND_SALES cs
                  WHERE cs.c_id = INVOICES.c_id)
 WHERE INVOICES.c_id IN (SELECT cs.s_id
                           FROM CUSTOMERS_AND_SALES cs)

使用JOINs

UPDATE INVOICES
  JOIN CUSTOMERS_AND_SALES cs ON cs.c_id = INVOICES.c_id
   SET s_id = cs.s_id,
       s_name = cs.s_name

- OMG Ponies

---

连接选项非常美妙。非常感谢 :) 只需要用 "(SELECT blah from blah blah blah)" 替换 "CUSTOMERS_AND_SALES"。 - Tommy O'Dell

非常好的例子。特别是第二个使用JOIN的例子。 - Nitesh Gupta

Answer 2

假设您的第一个表名为customers，那些没有销售员的客户的s_id为NULL。

UPDATE invoices JOIN customers USING (c_id)
SET invoices.s_id = customers.s_id, invoices.s_name = customers.s_name
WHERE customers.s_id IS NOT NULL;

我建议在开发环境中进行测试，或者先使用上面的JOIN运行SELECT查询来确保结果。

- Jason McCreary

---

无法在11g上工作ORA-00971：缺少基本单词SET - shareef

Answer 3

您可以创建一个视图来使您的UPDATE语句更简单。该视图应包含您的查询（在您的情况下，是关联客户和销售人员的查询）。然后像这样更新您的表格（在您的情况下为invoices）：

update TableToUpdate ttu, MyView mv
set ttu.column = mv.column
where ttu.key = mv.key

- Gruber

---

不适用于11g ORA-00971：缺少基本关键字SET - shareef