php - 使用相关表中的数据查询设置值

所以，当我为salesreport设计表时犯了一个大错误，如下所示

+----+------------+--------------+-------+
| id | company_id | company_code | value |
+----+------------+--------------+-------+
|  1 |          0 |           67 |   100 |
|  2 |          0 |           55 |   200 |
+----+------------+--------------+-------+

我最近才注意到它，并在公司表中添加一个名为company_id的新列，如下所示

+----+--------------+------+
| id | company_code | name |
+----+--------------+------+
|  1 |           55 | XX   |
|  2 |           67 | XA   |
+----+--------------+------+

过去我与company_code建立关系，因为我认为它总是唯一的，但不会自动递增，因此代码是从现有记录中的公司列表中手动创建的。

然后我意识到，通过使用id在表之间创建关系会更好，因此，在我的salesreport表上应该使用company_code而不是使用company_id指向公司表中的id列

现在在mysql数据库中已经有超过一千条使用company_code引用关系的记录，我想知道是否有一次我可以运行它来解决它的mysql查询？

我想出了这种查询

UPDATE salesreport SET company_id = '1' WHERE company_code = '67';

但是我认为既然表company和salesreport已经存在关系，为什么不能像这样

UPDATE salesreport SET company_id = company.id WHERE company_code = company.company_code;

但是我认为这不会起作用，它需要更多查询才能知道我选择了company表，然后将salesreport.company_code与company.company_code匹配，如果匹配，则将salesreport.company_id设置为company.id

好吧，我认为这是怎么回事...但是我不知道要执行的查询是什么..所以也许有人可以帮助我并为此提供一个懒惰的解决方案。

谢谢你

最佳答案

这是您要求的解决方案：

UPDATE salesreport
LEFT JOIN company ON company.company_code = salesreport.company_code
SET salesreport.company_id = company.id;

我创建了一个SQLFiddle供您测试结果：http://rextester.com/HNJ85353

她是一个完整的测试案例：

CREATE TABLE IF NOT EXISTS p1929_salesreport (id INTEGER PRIMARY KEY AUTO_INCREMENT,
                          company_id INTEGER,
                         company_code INTEGER,
                         value INTEGER);

INSERT INTO p1929_salesreport (company_id, company_code, value) VALUES (0, 67, 100);
INSERT INTO p1929_salesreport (company_id, company_code, value) VALUES (0, 55, 200);

CREATE TABLE IF NOT EXISTS p1929_company (id INTEGER PRIMARY KEY AUTO_INCREMENT,
                     company_code INTEGER,
                     name TEXT);

INSERT INTO p1929_company (company_code, name) VALUES (55, "XX");
INSERT INTO p1929_company (company_code, name) VALUES (67, "XA");

/* before changes */
SELECT * FROM p1929_company;
SELECT * FROM p1929_salesreport;

/* actual query */
UPDATE p1929_salesreport
LEFT JOIN p1929_company ON p1929_company.company_code = p1929_salesreport.company_code
SET p1929_salesreport.company_id = p1929_company.id;

/* after changes */
SELECT * FROM p1929_salesreport;

DROP TABLE p1929_salesreport;
DROP TABLE p1929_company;