Update a table based on the contents of two other tables. MySQL

Written by James McDonald

March 17, 2015

The goal was to populate the pack_size_id with the correct id number from the pack_size table, based on the mappings in the part-list table.

Table 1 (`items`)

+-------+--------------+
| code  | pack_size_id |
+-------+--------------+
| 50004 |           35 |
| 50005 |           36 |
| 50020 |           37 |
| 50021 |           35 |
| 50022 |           38 |
| 50024 |           37 |
| 50025 |           38 |
| 50026 |           36 |
| 50027 |           36 |
| 50028 |           36 |
| 50029 |           36 |
| 50032 |           39 |
| 50034 |           39 |
| 50035 |           39 |
| 50036 |           39 |
| 50038 |           40 |
| 50039 |           40 |
| 50041 |           40 |
| 50042 |           40 |
| 50043 |           38 |
+-------+--------------+

Table 2 (`part-list`)

+-------+--------------+
| code  | route        |
+-------+--------------+
| 50004 | ROUND3500    |
| 50005 | ROUND500     |
| 50020 | ROUND1000    |
| 50021 | ROUND3500    |
| 50022 | ROUND10000   |
| 50024 | ROUND1000    |
| 50025 | ROUND10000   |
| 50026 | ROUND500     |
| 50027 | ROUND500     |
| 50028 | ROUND500     |
| 50029 | ROUND500     |
| 50032 | RECT375(IML) |
| 50034 | RECT375(IML) |
| 50035 | RECT375(IML) |
| 50036 | RECT375(IML) |
| 50038 | RECT500      |
| 50039 | RECT500      |
| 50041 | RECT500      |
| 50042 | RECT500      |
| 50043 | ROUND10000   |
+-------+--------------+

Table 3 (`pack_sizes`)

+----+--------------+
| id | pack_size    |
+----+--------------+
| 35 | ROUND3500    |
| 36 | ROUND500     |
| 37 | ROUND1000    |
| 38 | ROUND10000   |
| 39 | RECT375(IML) |
| 40 | RECT500      |
| 41 | IML500X18    |
| 42 | IML250X32    |
| 44 | RECT375      |
| 45 | ROUND250     |
| 46 | OIL20L       |
| 47 | OIL2L        |
| 48 | OIL4L        |
| 49 | OIL200L      |
| 50 | OIL750ML     |
| 51 | OIL1L        |
| 52 | OIL500ML     |
| 53 | PALLECON     |
+----+--------------+
18 rows in set (0.00 sec)
update items t1
join `part-list` t2 on t1.code=t2.code
join `pack_sizes` t3 on t2.route=t3.pack_size
set t1.pack_size_id=t3.id
where t1.code=t2.code


select t1.code, t3.id from items t1 
join `part-list` t2 on t1.code=t2.code 
join `pack_sizes` t3 on t2.route=t3.pack_size 
where t1.code=t2.code;

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

You May Also Like…

How to Research a CPU Upgrade

How to Research a CPU Upgrade

Upgrade Time! Doing a lot of VMWare Workstation virtualization to create labs for self-study and training. Finding...