1

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

  • I'm a bit confused - is the value in the subcategories field in Product_categories = 1/2? And the value you want to finish with is example_category_1>example_category_2? If that is the case, then it's tricky. Can you change your schema to have two records in Product_categories, differing only by the values in the subcategories attribute - i.e. 1 for one record and 2 for the other? – Vérace May 21 '18 at 14:12
  • Yes, the value in subcategories is 1/2. And yes, the value i want to finish with is example_category_1>example_category_2 – JuanjoNC May 21 '18 at 14:16
  • Is there an upper limit for the number of subcategories per category? – Lennart May 21 '18 at 14:37
  • Yes, the limit number of categories in subcategories is 4 – JuanjoNC May 22 '18 at 6:52
  • Do You want to perform update using single query? or stored procedure is safe solution? Does Product_categories.subcategories can contain > symbol? Does Categories.name can contain / symbol? – Akina May 22 '18 at 7:09
0

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)
|improve this answer|||||
  • Thanks you very much for your answer. When I ejecute the update code in my phpMyAdmin console, it says: An expression was expected. (near "(" at position 19) Unexpected symbol (token) (near "(" at position 19) A new declaration was found, but there is no delimiter between this and the previous one. (near "SELECT" at position 20) Unexpected symbol (token) (near ")" at position 444) Unexpected symbol (token) (near "d" at position 446) A new declaration was found, but there is no delimiter between this and the previous one. (near "SET" at position 449) – JuanjoNC May 22 '18 at 8:25
  • Throw out Your phpMyAdmin. It tries to show his intellect, which is absent. It cannot execute a lot of semantically correct types of queries. – Akina May 22 '18 at 8:32
  • If it's possible in phpMyAdmin, execute the query while disabling syntax checking and sending query text directly to MySQL without modifications. – Akina May 22 '18 at 8:38
  • My bad. Importer first column is ´sku´ and not ´product_reference´. Edited and solved. Thank you guys! – JuanjoNC May 22 '18 at 10:22
  • Oh... this is impossible to predict. – Akina May 22 '18 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.