How to use an update statement with MySQL while using two inner joins and setting a table to a concatenation?

Question

I'm trying to set product.keywords to equal my category.name(s)

I have products that are in multiple categories. I want to concat each category name to product.keyword

Example:

SKU123 product.id is in category.id 1,2, and 3. The category.name is Category 1, Category 2, and Category 3 respectfully.

Sku123's product.keyword is currently "Awesome"

I'd like to write an UPDATE script that will update all my product.keyword

...

SKU123's product.keyword should = Awesome,Category 1, Category 2, and Category 3

...

Yes, comma separated, please :)

I have three tables.

table named "Product":

1. Column named "id"

Table named "category_product":

1. Column named "product_id"
2. Column named "category_id"

Table named "category":

1. Column named "name"
2. Column named "id"

product.id = product id

category_product.product_id = product id

category_product.category_id = category id

category.name = category name

category.id = category id

    select p.sku,p.name,p.keywords,c.name,c.id,group_concat(concat(p.keywords,',',c.name)) as new_keywords

    from category_product cp inner join category c on cp.category_id = c.id

inner join product p on p.id=cp.product_id

    where p.keywords >'' and p.sku='CJP-250-TMG10-1874470677';

above should give you an idea how everything is connected.

Sorry, this is only my second time using this site. Hopefully you guys have enough information!

Answer 1

Your JOIN is essentially correct. You need to do the CONCAT(p.keywords) outside GROUP_CONCAT(c.name), otherwise you'll repeat the original keyword with each category name. And to do it for every product, remove p.sku from the WHERE clause and use GROUP BY p.sku.

UPDATE products AS p
JOIN (
    SELECT p.sku, CONCAT(p.keywords, ',', GROUP_CONCAT(c.name)) AS new_keywords
    FROM products AS p
    JOIN category_product AS cp ON p.id = cp.product_id
    JOIN category AS c ON cp.category_id = c.id
    WHERE p.keywords != ''
    GROUP BY p.sku) AS p1 ON p.sku = p1.sku
SET p.keywords = p1.new_keywords

The SELECT statement that shows what it will store is:

SELECT p.sku, p.name, p.keywords, CONCAT(p.keywords, ',', GROUP_CONCAT(c.name)) AS new_keywords
FROM products AS p
JOIN category_product AS cp ON p.id = cp.product_id
JOIN category AS c ON cp.category_id = c.id
WHERE p.keywords != ''
GROUP BY p.sku