高级 MySql 查询：使用另一个表中的信息更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1202075/
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
Advanced MySql Query: Update table with info from another table
提问by superUntitled
I would like to update a table in mySql with data from another table.
我想用另一个表中的数据更新 mySql 中的一个表。
I have two tables "people" and "business". The people table is linked to the business table by a column called "business_id".
我有两个表“人”和“业务”。人员表通过名为“business_id”的列链接到业务表。
The necessary table structure, primary key is starred (Table: columns): People: *business_id, *sort_order, email Business: *business_id, email
必要的表结构，主键加星号（表：列）：人物：*business_id，*sort_order，email 业务：*business_id，email
I would like to update the business table email column with the email from the people table, something like this (I know I am missing something here):
我想用来自 people 表的电子邮件更新业务表电子邮件列，如下所示（我知道我在这里遗漏了一些东西）：
UPDATE business b SET email = (SELECT email from People p where p.business_id = b.business_id AND sort_order = '1') WHERE b.email = '';
Does this make sense? Is it possible?
这有意义吗？是否可以？
回答by ChssPly76
UPDATE business b, people p
SET b.email = p.email
WHERE b.business_id = p.business_id
AND p.sort_order = '1'
AND b.email = ''
回答by hobodave
Note, if sort_order is an INT, then don't use '1' - use 1:
请注意，如果 sort_order 是 INT，则不要使用 '1' - 使用 1：
UPDATE business b
JOIN People p
ON p.business_id = b.business_id
AND p.sort_order = '1'
SET b.email = p.email
WHERE b.email = '';
回答by king zecole
Try this, it works fine for me.
试试这个，它对我来说很好用。
Update table a, table b
Set a.importantField = b.importantField,
a.importantField2 = b.importantField2
where a.matchedfield = b.matchedfield;
