MySQL从其他表中插入数据 [英] MySQL insert data from other table
问题描述
这个问题类似于我以前的问题，除了这是INSERT而不是更新
我有两个表：联系人和公司。
This question is similar to my previous question except this is INSERT instead of update I have two tables: contacts and companies.
contacts has : id, group_id, company_id, email, company
companies has: id, group_id, name, email
因此使用此查询
UPDATE contacts c
INNER JOIN companies co ON c.company_id = co.id
SET c.group_id = co.group_id,
c.company = companies.name
我可以将更新数据从公司移动到contact.company_id = company.id的联系人。
I can move update data from company to contacts where there contact.company_id = company.id.
但是我该如何做INSERT而不是所有的公司，在那里没有联系呢？或者使事情更简单，我如何将所有公司表数据移动到联系人表数据。例如：
But how can I do INSERT instead for all the company where does not have contact yet? or to make things simpler, how can I move all companies table data into contacts table data. e.g:
Companies
id group_id name email
1 1 abc a@a.com
2 1 def d@d.com
3 1 ghi g@g.com
Contacts
id group_id company_id email company phone
1 1 1 a@a.com abc
2 1 2 d@d.com def
3 1 3 g@g.com ghi
所以我想要这样的条目，而没有值的那个将默认为NULL或无。
So I would like the entry like that, and for the one that is no value will be default to NULL or None
推荐答案
我想你想要的：
INSERT INTO Contacts (id,group_id,company_id,email,name)
SELECT co.id,co.group_id,co.id,co.email,co.name
FROM company co
LEFT JOIN contacts c ON co.id = c.company_id
WHERE c.company_id IS NULL
这将插入所有信息从公司的联系人，还没有在那里。列电话将留空，因为在该列的联系人中没有信息。
This will insert all the information from contacts in company that wasn't already there. the column phone will be left null, since there is no information in contacts for that column.
这篇关于MySQL从其他表中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
