MySQL:更新表中与其他查询结果匹配的所有行 [英] MySQL: Update all rows in a table matching results of another query

问题描述

我编写了一个查询，返回了与客户和销售人员相关联的行.

I've written a query returning rows associating Customers and 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

如何在MySQL中使用UPDATE来使发票表看起来像下面的表?

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

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

也就是说，如何更新我的发票表以在存在这种关系的地方包含正确的salesperson_id和salesperson_name?

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

请注意，在存在客户/销售人员关系的情况下，如果该客户有销售人员，则该客户的所有发票都应具有与其相关的销售人员.

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.

非常感谢:-)

推荐答案

使用子查询

最受支持的选项

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)

使用联接

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

这篇关于MySQL:更新表中与其他查询结果匹配的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！