Update fields with multiple values ​from different tables in MySQL

Question

I have 3 tables:

Product_categories (created from a .csv import). The limit number of categories in subcategories is 4.

product_reference | category_id | subcategories

REF.001           | 1           | 1/2

Categories (created from a .csv import)

id | name

1  | example_category_1
2  | example_category_2

Importer* (created to store all the data I need from the previous tables)

sku     | category

REF.001 | null

How could I update Importer to show the following? The limit number of categories in subcategories is 4.

sku     | category

REF.001 | example_category_1>example_category_2

*Data in Importer would be to export in .csv and be able to import in WooComerce, this is why I need to import the categories by this way. https://docs.woocommerce.com/document/product-csv-import-suite-column-header-reference/#section-4

Answer 1

Full example.

1) Create tables and data (code)

CREATE TABLE Product_categories (product_reference VARCHAR(8), category_id INT, subcategories VARCHAR(8));
INSERT INTO Product_categories VALUES
    ('REF.001',1,'1/2'),
    ('REF.002',2,'1/2/3/4');

CREATE TABLE Categories (id INT, name VARCHAR(32));
INSERT INTO Categories VALUES
    (1,'example_category_1'),
    (2,'example_category_2'),
    (3,'example_category_3'),
    (4,'example_category_4');

CREATE TABLE Importer (product_reference VARCHAR(8), category VARCHAR(128));
INSERT INTO Importer (product_reference) VALUES
    ('REF.001'),
    ('REF.002');

2) Check data (console output)

mysql> SELECT * FROM Product_categories;
+-------------------+-------------+---------------+
| product_reference | category_id | subcategories |
+-------------------+-------------+---------------+
| REF.001           |           1 | 1/2           |
| REF.002           |           2 | 1/2/3/4       |
+-------------------+-------------+---------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM Categories;
+------+--------------------+
| id   | name               |
+------+--------------------+
|    1 | example_category_1 |
|    2 | example_category_2 |
|    3 | example_category_3 |
|    4 | example_category_4 |
+------+--------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM Importer;
+-------------------+----------+
| product_reference | category |
+-------------------+----------+
| REF.001           | NULL     |
| REF.002           | NULL     |
+-------------------+----------+
2 rows in set (0.00 sec)

3) Perform update (code)

UPDATE Importer i, (SELECT pc.product_reference,GROUP_CONCAT(c.name ORDER BY n.n SEPARATOR '>') category
                    FROM (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) n 
                    JOIN Product_categories pc
                    JOIN Categories c ON SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('',pc.subcategories,'////'),'/',n.n),'/',-1) = c.id
                    GROUP BY pc.product_reference
                   ) d
SET i.category = d.category
WHERE i.product_reference = d.product_reference;

4) Check result (console output)

mysql> SELECT * FROM Importer;
+-------------------+-----------------------------------------------------------------------------+
| product_reference | category                                                                    |
+-------------------+-----------------------------------------------------------------------------+
| REF.001           | example_category_1>example_category_2                                       |
| REF.002           | example_category_1>example_category_2>example_category_3>example_category_4 |
+-------------------+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)