MySQL从其他表中插入数据

问题描述：

这个问题类似于我以前的问题，除了这是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从其他表中插入数据

相关推荐