其他表的MySQL更新表

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 "".

目前,联系人。group_id值为0(最近添加了这个列)，contac .company值为“”。

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

我使用phpmyadmin来运行这个查询。

2 个解决方案

#1

Mysql lets you do JOINS in your UPDATE statements:

Mysql允许您加入更新语句:

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.

您还可以执行覆盖多个表的更新操作。但是，不能对多表更新使用ORDER BY或LIMIT。table_references子句列出了连接中涉及的表。

#2

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

#1