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 *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.

You May Also Like…

PHPSECLIB3 Client Algorithms

Recently our EDI supplier notified that they were changing their SFTP encryption and data integrity algorithms I use...