MySQL：更新匹配另一个查询结果的表中的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3742893/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Update all rows in a table matching results of another query
提问by Tommy O'Dell
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.
我的数据库中还有一个表（称为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
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 :-)
多谢:-)
回答by OMG Ponies
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
使用 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
回答by Jason McCreary
Assuming your first table is named customersand those customers without a salesperson have an s_idof 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 SELECTquery using the JOINabove first to ensure the results.
我建议首先SELECT使用JOIN上述方法在开发中进行测试或运行查询以确保结果。
回答by Gruber
You can create a view to make your UPDATEstatement simple. The view should contain your query (in your case the query that associates customers and salespeople). Then update your table (invoicesin your case) like this:
您可以创建一个视图来UPDATE简化您的语句。该视图应包含您的查询（在您的情况下是关联客户和销售人员的查询）。然后invoices像这样更新你的表（在你的情况下）：
update TableToUpdate ttu, MyView mv
set ttu.column = mv.column
where ttu.key = mv.key
