mysql - 用其他列中的值替换列值

Question

我有一个表“公司”，其中包含彼此有关系的公司。我想将“合作伙伴”栏中的公司名称替换​​为对应的ID。

ID  Company     Partner

1   Company A   Company B
2   Company B   Company C
3   Company C   Company B

应该变成：

ID  Company     Partner

1   Company A   2
2   Company B   3
3   Company C   2

我已经尝试过了，但是收到一个错误，即子查询返回多个结果：

UPDATE Companies c1
SET Partner =
(SELECT DISTINCT c2.ID FROM Companies c3 JOIN Companies c2 ON c3.Partner = c2.Company
    WHERE c1.Partner = c3.Partner
)

标签： mysqlsql

Answer 1

使用JOIN：

UPDATE Companies c JOIN
       Companies cp
       ON c.Partner = cp.Company
    SET c.Partner = cp.id;