MySQL: insert values in new table from an old one using another mapping table

Question

I have a table in my application which maps products to categories product_to_category:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|          1 |           5 |
|          2 |           4 |
|          3 |           7 |
|          4 |           8 |
|          4 |           1 |
|          5 |          10 |
+------------+-------------+

Now I have to create another table similar to this but with updated category_ids based on a third mapping table:

new_product_to_category

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|          1 |             |
|          2 |             |
|          3 |             |
|          4 |             |
|          4 |             |
|          5 |             |
+------------+-------------+

new_mapping

+----------+---------+
| old_cats | new_cat |
+----------+---------+
|    1,2,3 |      91 |
|    12,11 |      92 |
|    55,23 |      93 |
|    43,78 |      94 |
|    4,5,6 |      95 |
| 33,37,39 |      96 |
+----------+---------+

If I could do a join with IN clause then I would do something like this:

INSERT INTO new_product_to_category (product_id, category_id)
SELECT pc.product_id, nm.new_cat
FROM product_to_category as pc
JOIN new_mapping as nm
ON pc.category_id IN nm.old_cats

But I guess I can't use IN clause in JOIN, how I can achieve the same functionality?

@Sami because I can't find a way to join these tables when I can't use `IN` clause — thelaw, Sep 18 '19 at 08:40
Do not store multiple values in a single column. Do not store numbers as strings. Do declare foreign key relationships. Your `new_mapping` table is just very, very, very bad. — Gordon Linoff, Sep 18 '19 at 11:08
@GordonLinoff I know but a customer created in excel in order to show the new mapping.... — thelaw, Sep 18 '19 at 12:50

score 1 · Accepted Answer · answered Sep 18 '19 at 08:40

You could do an update join with the help of FIND_IN_SET:

UPDATE new_product_to_category t1
INNER JOIN new_mapping t2
    ON FIND_IN_SET(t1.product_id, t2.old_cats) > 0
SET t1.category_id = t2.new_cat;

If you really wanted to avoid using a join, you could also phrase this update using a correlated subquery:

UPDATE new_product_to_category t1
SET category_id =
    (SELECT t2.new_cat FROM new_mapping t2
     WHERE FIND_IN_SET(t1.product_id, t2.old_cats) > 0);

MySQL: insert values in new table from an old one using another mapping table

1 Answers1