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

本文介绍了MySQL:更新表中匹配另一个查询结果的所有行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我编写了一个查询，返回与 Customers 和 Salespeoeple 相关联的行.

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

我的数据库中还有一个表(称为invoices)，如下所示.

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)

使用 JOIN

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