CODEWITHSUNDEEP
mysql
inf3rno
inf3rno

Reputation: 26129

MySQL - select one row, but insert multiple rows after processing data

I have 3 tables in a webshop:

jos_vm_category
    category_id | distributor_code | ...
jos_vm_product
    product_id | product_sku | ...
jos_vm_product_category_xref
    category_id | product_id

and I uploaded fresh data to a temporary table:

temp_product_traversal
    product_sku | category_distributor_code | ...

I have to refresh the jos_vm_product_category_xref table.

The jos_vm_category.distributor_code is same than temp_product_traversal.category_distributor_code, and it can be 3,6,9 characters long in both tables. The 3 chars long code is the main category, and the 6,9 long are the code of the subcategories. I have to put the product into its subcategory and every super category if that. For example: If the code is VGAATIPCE, then I have to insert the product to 3 categories:

VGAATIPCE, VGAATI, VGA

so by this situation we need 3 new rows in jos_vm_product_category_xref.

Is it possible to insert the 3 new rows into the jos_vm_product_category_xref with one mysql request?

Upvotes: 0

Views: 759

Answers (2)

inf3rno
inf3rno

Reputation: 26129

I have another solution with union:

INSERT jos_vm_product_category_xref(product_id,category_id)
SELECT p.product_id,c.category_id
FROM 
    (
        (
            SELECT product_sku,LEFT(category_distributor_code,9) AS distributor_code
            FROM temp_product_traversal
        )
        UNION
        (
            SELECT product_sku,LEFT(category_distributor_code,6) AS distributor_code
            FROM temp_product_traversal
        )
        UNION
        (
            SELECT product_sku,LEFT(category_distributor_code,3) AS distributor_code
            FROM temp_product_traversal
        )
    ) AS trav,
    jos_vm_product AS p,
    jos_vm_category AS c
WHERE
    p.product_sku=trav.product_sku
AND
    c.distributor_code=trav.category_distributor_code

Upvotes: 0

Darc
Darc

Reputation: 706

A JOIN expression can, in many ways, mimic a WHERE expression. A few ORs in your JOIN and you should be able to handle what you're looking for.

INSERT jos_vm_product_category_xref(category_id, product_id)
SELECT c.category_id, p.product_id from temp_product_traversal trav
    JOIN jos_vm_product p ON trav.product_sku = p.product_sku
    JOIN jos_vm_category c ON LEFT(trav.category_distributor_code,3) = c.distributor_code 
        OR LEFT(trav.category_distributor_code,6) = c.distributor_code 
        OR trav.category_distributor_code = c.distributor_code

Upvotes: 1

Related Questions