I would be grateful for some help with a MySQL Stored Procedure that I have written. It is running on MySQL 5.6.11 on Windows and I have read and re-read the MySQL docs and carried out a number of google searches.
I have database for storing information associated with products and in some cases I want to create new associated information by copying from one product record and inserting for another product record or from one product record and inserting to all product records in a specific product group. I can successfully copy from one one product record and insert for another product record but I am having trouble copying from one product record then inserting for all product records in a specific product group.
Initial research suggest that a cursor as part of a stored procedure may provde a solution. In terms of the problem I have there are two tables involved, one bullets, this holds bullet points and I want to copy them. The second table is the master table and it holds products data such as product code and the product group code associated with the product code.
I wish to create new records in the bullets table by:
INSERT INTO bullets (product_code, bullet_text) SELECT 'fromCode', bullet_text
FROM bullets WHERE product_code = 'toCode'
DECLARE product_code_from_group VARCHAR(30);
DECLARE from_product_code VARCHAR(30);
DECLARE done INT DEFAULT FALSE;
DECLARE cur_get_product_codes CURSOR FOR
SELECT product_code FROM master
WHERE product_group = to_product_group ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
FETCH cur_get_product_codes INTO product_code_from_group;
IF done THEN
INSERT INTO bullets (product_code, bullet_text)
SELECT product_code_from_group, bullet_text
WHERE product_code = from_product_code;
If I understand correctly all you need is
CROSS JOIN. Try
INSERT INTO bullets (product_code, bullet_text) SELECT m.product_code, b.bullet_text FROM bullets b CROSS JOIN master m WHERE b.product_code = 10001 AND m.product_group = 3 AND m.product_code <> 10001;
Here is SQLFiddle demo.
Now you can wrap it up into a stored procedure if you went to
CREATE PROCEDURE copy_bullets_test (IN product_code_from INT, IN product_group_to INT) INSERT INTO bullets (product_code, bullet_text) SELECT m.product_code, b.bullet_text FROM bullets b CROSS JOIN master m WHERE b.product_code = product_code_from AND m.product_group = product_group_to AND m.product_code <> product_code_from;
And use it
CALL copy_bullets_test(10001, 3);
Here is SQLFiddle demo for that case.