mysql 批量更新(可用存储过程)

发布网友发布时间：2022-04-08 19:24

共3个回答

懂视网时间：2022-04-08 23:45

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(产品品牌)

标签：

热心网友时间：2022-04-08 20:53

update tableName set click=click+
case
when id=1 and ip='198.25.54.45' and date=11254987 then 2
when id=1 and ip='185.45.14.47' and date=11547899 then 4
when id=2 and ip='184.78.45.14' and date=13457489 then 3
end;

热心网友时间：2022-04-08 22:11

不知道你这个click=click+2,+3,+4 是怎么个规律？？是所有数据都加的一样的？