MySQL：更新表中与其他查询的结果匹配的所有行

I've written a query returning rows associating Customers and Salespeoeple.

我写了一个查询返回关联Customers和Salespeoeple的行。

Note that the query joins several database tables. And note that not all customers have a salesperson.

请注意，查询会连接多个数据库表。请注意，并非所有客户都有销售人员。

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

I also have a single table (called invoices) in my database that looks like this.

我的数据库中还有一个表（称为发票），如下所示。

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

How can I use UPDATE in MySQL to make my invoices table look like the table below?

如何在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

That is to say, how can I update my invoice table to include the correct salesperson_id and salesperson_name, where that relationship exists?

也就是说，如何更新我的发票表以包含正确的salesperson_id和salesperson_name，该关系存在于何处？

Note that where a Customer/Salesperson relationship exists, all invoices for that customer should have the salesperson associated with it, if there is a salesperson for that customer.

请注意，如果存在客户/销售人员关系，则该客户的所有发票应该与销售人员关联，如果该客户有销售人员。

Thanks kindly :-)

谢天谢地:-)

3 个解决方案

#1

Using subqueries

Most widely supported option

最广泛支持的选项

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)

Using 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

#2

Assuming your first table is named customers and those customers without a salesperson have an s_id of NULL

假设您的第一个表名为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;

I suggest testing in development or running a SELECT query using the JOIN above first to ensure the results.

我建议首先使用上面的JOIN测试开发或运行SELECT查询以确保结果。

#3

You can create a view to make your UPDATE statement simple. The view should contain your query (in your case the query that associates customers and salespeople). Then update your table (invoices in your case) like this:

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

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