mysql - 规范化 MySQL 数据库未更新

Question

我想做什么:

我有一个包含 3 个表的规范化数据库。我想要做的就是将所有 MODEL 值替换为 SERIES 值，其中 OEMID 等于 8。

我以每个表中的 5 行为例:

系列表

+--------+----------+
| series | seriesid |
+--------+----------+
| 1001   |        7 |
| 10036  |        8 |
| 10067  |        9 |
| 1007   |       10 |
| 10076  |       11 |
+--------+----------+

模型表

+---------+----------------------------------------+
| ModelId | Model                                  |
+---------+----------------------------------------+
|    6694 | 1001 - 71 Flexi Unit Planter - 30Aug01 |
|    8264 | 10036 - 315SJ Backhoe Loader           |
|    8263 | 10067 - 310J Backhoe Loader - 20Jul17  |
|    6693 | 1007 - 60 Lawn Tractor - 30Aug01       |
|    8262 | 10076 - 853JH Tracked Harvester        |
+---------+----------------------------------------+

主数据表

+------------+----------+-------------+------------------------------+------+-------+----------+---------+-----------------+----------------+
| partnumber | partname | description | imageurl                     | qty  | oemid | seriesid | modelid | functiongroupid | assemblynameid |
+------------+----------+-------------+------------------------------+------+-------+----------+---------+-----------------+----------------+
| A32        | Lens     | RED         | AP3415_________UN01JAN94.gif | 2    |     8 |        7 |    6694 |           12318 |         449980 |
| 14274      | Nut      | M10         | TX1061736______UN20JUL09.gif | 1    |     8 |        8 |    8264 |           13996 |         884056 |
| C1115      | HC       | NLA ORDER   | 0000897883____________A2.gif | 1    |     8 |        9 |    8263 |           13962 |         880092 |
| 03H1626    | BOLT     | 1/4 X 1/2   | M5004__________UN02JAN94.gif | 1    |     8 |       10 |    6693 |           17549 |          89782 |
| 1854       | Screw    | M10 X 80    | TX1030795______UN25OCT07.gif | 4    |     8 |       11 |    8262 |           14029 |         891643 |
+------------+----------+-------------+------------------------------+------+-------+----------+---------+-----------------+----------------+

因此，让我们获取 masterdata 表中的第一行。我想用 1001 - 71 Flexi Unit Planter - 30Aug01

替换 1001 系列

我尝试过的:

我首先尝试的是:

1. 将所有不同的模型插入到 oemid=8 的系列表中

   INSERT INTO SERIES(SERIES) SELECT DISTINCT M.MODEL FROM MASTERDATA MD JOIN MD.MODELID=M.MODELID WHERE MD.OEMID=8;
   

2. 由于 SERIESID 列是自动递增的，因此我不需要手动设置 ID。然后我尝试更新 MASTERDATA 表以反射(reflect)此更改:

   UPDATE MASTERDATA MD JOIN MODEL M ON M.MODELID=MD.MODELID JOIN SERIES S ON S.SERIES=M.MODEL SET MD.SERIESID=S.SERIESID WHERE MD.OEMID=8;
   

此查询的想法是使用 MODELID 将 MODEL 表连接到 MASTERDATA 表，因为我没有更改任何 MODEL 值。然后，我将 SERIES 表加入到 MODEL 表中，其中 MODEL 等于 SERIES。这将使我获得每个 MODEL 的 SERIESID。然后，我只需将 MASTERDATA 中的 SERIESID 设置为 SERIES 表中的 SERIESID。但是，当我运行它时，这不起作用。

我唯一剩下的选择是编写一个外部程序来处理这个问题，但如果可能的话我宁愿不走这条路。谁能提供解决方案？

不确定这是否重要，但我在 Linux CentOS 7 上运行数据库。

提前致谢。

编辑 1:

这是插入 MODEL 值后 SERIES 表的样子:

+----------------------------------------+-----------+
| series                                 | seriesid  |
+----------------------------------------+-----------+
| 1001                                   |         7 |
| 10036                                  |         8 |
| 10067                                  |         9 |
| 1007                                   |        10 |
| 10076                                  |        11 |
| 1001 - 71 Flexi Unit Planter - 30Aug01 |       256 |
| 10036 - 315SJ Backhoe Loader           |       257 |
| 10067 - 310J Backhoe Loader - 20Jul17  |       258 |
| 1007 - 60 Lawn Tractor - 30Aug01       |       259 |
| 10076 - 853JH Tracked Harvester        |       260 |
+----------------------------------------+-----------+

我的计划是使用您在上面的 2. 中看到的查询，然后对系列运行查询以删除 SERIESID 中不存在的任何行MASTERDATA 表。

编辑 2:

我的目标是让 MASTERDATA 看起来像这样:

+------------+----------+-------------+------------------------------+------+-------+------------+---------+-----------------+----------------+
| partnumber | partname | description | imageurl                     | qty  | oemid | seriesid   | modelid | functiongroupid | assemblynameid |
+------------+----------+-------------+------------------------------+------+-------+------------+---------+-----------------+----------------+
| A32        | Lens     | RED         | AP3415_________UN01JAN94.gif | 2    |     8 |        256 |    6694 |           12318 |         449980 |
| 14274      | Nut      | M10         | TX1061736______UN20JUL09.gif | 1    |     8 |        257 |    8264 |           13996 |         884056 |
| C1115      | HC       | NLA ORDER   | 0000897883____________A2.gif | 1    |     8 |        258 |    8263 |           13962 |         880092 |
| 03H1626    | BOLT     | 1/4 X 1/2   | M5004__________UN02JAN94.gif | 1    |     8 |        259 |    6693 |           17549 |          89782 |
| 1854       | Screw    | M10 X 80    | TX1030795______UN25OCT07.gif | 4    |     8 |        260 |    8262 |           14029 |         891643 |
+------------+----------+-------------+------------------------------+------+-------+------------+---------+-----------------+----------------+

Answer 1

试试下面这个选择查询，你的要求对我来说不是很清楚，但仍然希望这对你有所帮助，

select 
   C.partnumber,
   C.partname,
   C.description,
   C.imageurl, 
   C.qty, 
   C.oemid, 
   A.seriesid, 
   B.ModelId, 
   C.functiongroupid, 
   C.assemblynameid 
from 
   Series A, 
   Model B, 
   Masterdata C 
where 
   C.modelid=B.ModelId and 
   A.series = SUBSTRING_INDEX(B.Model,' - ', 1); 

注意 SUBSTRING_INDEX 用于假定 Model 表中 Model 列的第一部分与Series 表中的 series 列值。如果它直接匹配您在EDIT 1中提到的值，那么我们可以直接等同。

如果上面的选择查询为您提供了所需的输出，那么要直接在 Masterdata 中更新 seriesid 我们可以使用下面的更新查询，

update 
   Series A, 
   Model B, 
   Masterdata C 
set 
   C.seriesid = A.seriesid 
where 
   C.modelid=B.ModelId and 
   A.series = SUBSTRING_INDEX(B.Model,' - ', 1); 

如果想删除除 which 匹配之外的条目，那么我们可以使用相同的 where 子句的反向大小写，希望这会有所帮助，