CODEWITHSUNDEEP
phpmysqlsqldatabasesql-update
Harts
Harts

Reputation: 4093

MySQL update table from other table

I have two tables: contacts and companies.

contacts has : id, group_id, company_id, email, company
companies has: id, group_id, name

At the moment, contacts.group_id value is 0 (recently added the column) so does contacts.company value is "".

How should I move the data on 

companies.group_id to contacts.group_id and companies.name to contacts.company 
based on contacts.company_id = companies.id?

I tried the following query but gives me error

UPDATE contacts SET contacts.group_id=companies.group_id 
FROM companies WHERE contacts.company_id=companies.id;

It gives me this error

#1064 - You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'FROM companies WHERE 
contacts.company_id=companies.id' at line 1

I use phpmyadmin to run this query

Upvotes: 1

Views: 117

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

Mysql lets you do JOINS in your UPDATE statements:

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

From the docs:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. The table_references clause lists the tables involved in the join.

Upvotes: 2

Lajos Veres
Lajos Veres

Reputation: 13725

I think this could work for you:

update
  contacts,
  companies
set
  contacts.group_id=companies.group_id, 
  contacts.company=companies.name
where
  contacts.company_id = companies.id

Upvotes: 1

Related Questions