Insert value from one table/column based on value from another table/column-CodePudding

I am trying to import a csv, unpivot the data and INSERT INTO product_attribute table.

I have a table called attributes

attribute_id	attribute_name	attribute_value
1	colour	black
2	colour	green
3	colour	blue
4	size	small
5	size	medium
6	size	large

I have a table called products

product_id	sku	qty
1	test3	13
2	test2	17
3	test1	5

I have a table called product_attribute which is linked between both tables above.

product_id	attribute_id

the csv data is as follows which imports into a temp folder called import.

sku	colour	size
test1	black	small
test2	green	large
test3	blue	medium

so far I have managed to unpivot the csv/table using the following query

SELECT sku, 'colour' attribute_name, colour attribute_value
FROM import
UNION ALL
SELECT sku, 'size' attribute_name, size attribute_value
FROM import
UNION ALL

the data then looks like this

sku	attribute_name	attribute_value
test1	colour	black
test2	colour	green
test3	colour	blue
test1	size	small
test2	size	large
test3	size	medium

I need to somehow add a INSERT INTO query with the unpivot query so the product_attribute table looks like the following

product_id	attribute_id
3	1
2	2
1	3
3	4
2	6
1	5

I believe a subquery would help me achieve this but I'm not sure how to put it together when the value is based on another column.

Any help greatly appreciated thankyou.

CodePudding user response：

Use your code in a subquery and join it to the two tables using the common columns.

INSERT INTO product_attribute(product_id, attribute_id)
    SELECT p.product_id, a.attribute_id
    FROM (
        SELECT sku, 'colour' attribute_name, colour attribute_value
        FROM import
        UNION ALL
        SELECT sku, 'size' attribute_name, size attribute_value
        FROM import
        UNION ALL
    ) as u
    JOIN products p   USING(sku)
    JOIN attributes a USING(attribute_name, attribute_value)