CODEWITHSUNDEEP
phpmysqlsqldatabasesql-insert
Harts
Harts

Reputation: 4093

MySQL insert data from other table

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

so using this query

UPDATE contacts c
INNER JOIN companies co ON c.company_id = co.id 
SET c.group_id = co.group_id,
    c.company = companies.name

I can move update data from company to contacts where there contact.company_id = company.id.

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    [email protected]
2    1          def    [email protected]
3    1          ghi    [email protected]

Contacts
id   group_id   company_id   email     company   phone
1    1          1            [email protected]   abc
2    1          2            [email protected]   def
3    1          3            [email protected]   ghi

So I would like the entry like that, and for the one that is no value will be default to NULL or None

Upvotes: 1

Views: 129

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

I think that you want:

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.

Upvotes: 2

Brixius111
Brixius111

Reputation: 1

I believe you would need to do an outer join between the 2 tables in your select statement then look for a field in your destination table to be null and only select those rows.

insert into t1 (f1,f2,f3) select s1,s2,s3 from sourcetable as st 
  left outer join t1 on 
      t1.f1=st.s1,
      t1.f2=st.s2,
      t1.f3=st.s3
  where t1.id is null;

This way you only get the rows from st where there is no id in t1.

Upvotes: 0

Related Questions