php - MySQL 从其他表更新表

Question

我有两个表:联系人和公司。

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

目前，contacts.group_id 值为 0(最近添加的列)，contacts.company 值为“”。

我应该如何移动数据

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

我尝试了以下查询，但出现错误

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

它给我这个错误

#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

我使用 phpmyadmin 来运行这个查询

Answer 1

Mysql 允许您在 UPDATE 语句中执行 JOINS:

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

来自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.