Normalized MySQL database not updating

What I want to do:

I have a normalized database containing 3 tables. All I want to do is replace all the MODEL values with the SERIES values where the OEMID equals 8.

MODEL

SERIES

OEMID

I've taken 5 rows from each table as an example:

Series Table

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


Model Table

+---------+----------------------------------------+
| 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 |
+---------+----------------------------------------+


Masterdata Table

+------------+----------+-------------+------------------------------+------+-------+----------+---------+-----------------+----------------+
| 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 |
+------------+----------+-------------+------------------------------+------+-------+----------+---------+-----------------+----------------+


So lets take the first row in the masterdata table. I want to replace the series 1001 with 1001 - 71 Flexi Unit Planter - 30Aug01

1001

1001 - 71 Flexi Unit Planter - 30Aug01

What I've Tried:

The first thing I tried was:

Inserting all distinct models into the series table where the oemid=8

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


Since the SERIESID column is auto-incrementing, I didn't need to manually set the id. I then attempted to update the MASTERDATA table to reflect this change:

SERIESID

MASTERDATA

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;


The idea for this query is that the MODEL table is joined to the MASTERDATA table using MODELID since I'm not changing any MODEL values. Then, I join the SERIES table on the MODEL table where the MODEL is equal to SERIES. This will let me get the SERIESID for each MODEL. Then I simply set the SERIESID in MASTERDATA to the SERIESID in the SERIES table. However, this did not work when I ran it.

MODEL

MASTERDATA

MODELID

MODEL

SERIES

MODEL

MODEL

SERIES

SERIESID

MODEL

SERIESID

MASTERDATA

SERIESID

SERIES

My only remaining option is to write an external program to deal with this, but I'd prefer not to take this route if possible. Can anyone offer a solution?

Not sure if it matters, but I'm running the DB on Linux CentOS 7.

Thanks in advance.

EDIT 1:

This is what the SERIES table looks like after inserting the MODEL values:

SERIES

MODEL

+----------------------------------------+-----------+
| 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 |
+----------------------------------------+-----------+


My plan was to use the query you can see above on 2. then run a query on series to remove any rows where the SERIESID doesn't exist in the MASTERDATA table.

2.

SERIESID

MASTERDATA

EDIT 2:

I'm aiming for the MASTERDATA to look like this:

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 |
+------------+----------+-------------+------------------------------+------+-------+------------+---------+-----------------+----------------+


EDIT:

1 Answer
1

Try this select query below, your requirement is not that clear for me but still hope this will help you some how,

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);


Note: SUBSTRING_INDEX is used assuming first part of the Model column in Model table is same as the series column value in Series table. If its directly matching value as you mentioned in EDIT 1 in question, then we can directly equate.

SUBSTRING_INDEX

Model

Model

series

Series

if above select query gives you the required output, then to update seriesid directly in Masterdata we can use below update query,

seriesid

Masterdata

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);


If want to remove the entries other than which matches, then we can use the reverse case of this same where clause, Hope this will be of some help,

MASTERDATA

Masterdata C

(select * from Masterdata limit 10000)C

where

and A.series = SUBSTRING_INDEX(B.Model,' - ', 1);

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.