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

Question

所以，当我为 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 创建表之间的关系会更好，因此不应使用 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

好吧，我认为事情就是这样......但我不知道执行此操作的查询是什么......所以也许有人可以帮助我并为此提供一个懒惰而优雅的解决方案。

顺便谢谢你。

Answer 1

以下是针对您的请求的解决方案:

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

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

Herу 是一个完整的测试用例:

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;