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

 

执行语句

DELIMITER $$DROP PROCEDURE IF EXISTS jsjh_goods_property_value_update$$CREATE PROCEDURE jsjh_goods_property_value_update()BEGINDECLARE row_base_brand varchar(50);#定义变量品牌DECLARE row_title varchar(50);#定义tleteDECLARE row_value varchar(50);#定义valueDECLARE done INT;-- 定义游标DECLARE rs_cursor CURSOR FORSELECT 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<>''UNIONSELECT 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:LOOPFETCH rs_cursor INTO row_base_brand,row_title;IF done=1 THENleave cursor_loop;END IF;-- 更新表IF row_title IS NULL AND row_base_brand IS NOT NULL THENINSERT 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 THENUPDATE jsjh_goods_property_value SET deleted=UNIX_TIMESTAMP() WHERE title=row_title;END IF;END LOOP cursor_loop;CLOSE rs_cursor;END$$DELIMITER ;