Match unrelated tables (MySQL)

Occasionally, I want to match unrelated tables. That sounds like ordering a steak in a vegetarian restaurant, but there is a real use case behind it: Assigning EAN numbers to products:

• Table ean contains 1,000,000 reserved EAN numbers (some of which may be used already)
• Table product contains a bunch of rows without value for the ean column

How to assign EAN numbers to these products and conversely, update the EAN table with the SKUs of these products?

General approach - Use serial numbers

This is the solution that I've been using since 2016. It seems a bit cumbersome, but it works, and it is quite safe (the table with EANs is one of the few tables that I really don't want to mess up, plus that it is really big):

• Create temporary tables ean_tmp and product_tmp that only contain the records that need to be matched
• Add an autonumber primary key to both tables
• Match the tables using these pks and update both
• Update the two original tables from these temporary tables.

Library

• It seems to be too complicated to create a sproc that can handle the general case (like any kind of table where EAN codes are needed): It's much easier, faster and less critical to just copy-paste-adjust the last sproc - See History below
• Everything is handled by a single sproc. No need for additional scripts or whatever.

History

• Pre 2020.01: This sproc (whatever it was called - Maybe just ean_assign_codes) was part of "bal_dwh_op" & "bal_dwh_process"
• 2020.01: Refactored, as part of "bal_dwh_op"
• 2021.02: Updated these comments (@ bal_dwh_op)
• 2022.12: Moved sproc ean_assign_codes to bal_dwh_org, removed it from other locations
• 2022.12.12: Copy-paste-adjusted ean_assign_codes to ean_assign_oem_bal (228 lines of code)
• 2022.12.12: Copy-paste-adjusted ean_assign_codes to ean_assign_product_new_various_202212 (224 lines of code)

See also

• Match ongerelateerde tabellen (MySQL)