MYSQL存储过程：批量更新数据2(产品品牌)

 

执行语句

DELIMITER $$
DROP PROCEDURE IF EXISTS jsjh_goods_property_value_update$$
CREATE PROCEDURE jsjh_goods_property_value_update()
BEGIN
DECLARE row_base_brand varchar(50);#定义变量品牌
DECLARE row_title varchar(50);#定义tlete
DECLARE row_value varchar(50);#定义value
DECLARE done INT;
-- 定义游标
DECLARE rs_cursor CURSOR FOR
SELECT a.base_brand,b.title FROM jsjh_goods_item a LEFT JOIN jsjh_goods_property_value b ON (b.title=a.base_brand AND b.property_id=1 AND b.deleted=0) WHERE a.base_brand<>''
UNION
SELECT a.base_brand,b.title FROM jsjh_goods_item a RIGHT JOIN jsjh_goods_property_value b ON (b.title=a.base_brand AND a.base_brand<>'') WHERE b.property_id=1 AND b.deleted=0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN rs_cursor; 
cursor_loop:LOOP
FETCH rs_cursor INTO row_base_brand,row_title;
IF done=1 THEN
leave cursor_loop;
END IF;
-- 更新表
IF row_title IS NULL AND row_base_brand IS NOT NULL THEN
INSERT INTO jsjh_goods_property_value(property_id,value,title,showed) values(1,row_base_brand,row_base_brand,1);
END IF;
IF row_base_brand IS NULL AND row_title IS NOT NULL THEN
UPDATE jsjh_goods_property_value SET deleted=UNIX_TIMESTAMP() WHERE title=row_title;
END IF;
END LOOP cursor_loop;
CLOSE rs_cursor;
END$$
DELIMITER ;