MYSQL存储过程：批量更新数据2(产品品牌)
时间：2021-07-01 10:21:17
帮助过：2人阅读
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 ;
MYSQL存储过程：批量更新数据2(产品品牌)
标签：
