mysql使用游标遍历数据进行批量针对性更新数据，急求mysql大神解答

根据你的资料，正确的答案应该如下：望采纳

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_updateUd18`(`Param` int(11))

BEGIN

declare Rcount,i,j,k,Rparentid,myId int(11) default 0

declare Robjname,Robjname23,Robjname45,Robjname67,Robjname89 varchar(9) default ""

select count(id) into Rcount from ud18

repeat

select id,objname into myId,Robjname from ud18 order by id limit i,1

set Robjname23=SUBSTRING(Robjname,2,2)

set Robjname45=SUBSTRING(Robjname,4,2)

set Robjname67=SUBSTRING(Robjname,6,2)

set Robjname89=SUBSTRING(Robjname,8,2)

if Robjname89>0 then

set @sql=concat("select id into @abc from ud18 where objname like '",left(Robjname,7),"00","'")

prepare stmt from @sql

EXECUTE stmt

set Rparentid=@abc

update ud18 set parentid=Rparentid where id=myId

elseif Robjname67>0 then

set @sql=concat("select id into @abc from ud18 where objname like '",left(Robjname,5),"0000","'")

prepare stmt from @sql

EXECUTE stmt

set Rparentid=@abc

update ud18 set parentid=Rparentid where id=myId

elseif Robjname45>0 then

set @sql=concat("select id into @abc from ud18 where objname like '",left(Robjname,3),"000000","'")

prepare stmt from @sql

EXECUTE stmt

set Rparentid=@abc

update ud18 set parentid=Rparentid where id=myId

elseif Robjname23>0 then

-- set @sql=concat("select id into @abc from ud18 where objname like '",left(Robjname,1),"00000000","'")

-- prepare stmt from @sql

-- EXECUTE stmt

-- set Rparentid=@abc

update ud18 set parentid=0 where id=myId

else

set j=0

end if

set i=i+1

until i>Rcount end repeat

END

CREATE PROCEDURE init_reportUrl()

BEGIN

DECLARE s INT DEFAULT 0

DECLARE r_id bigint(10)

DECLARE report CURSOR FOR select distinct id as r_id FROM ReportHotLine

-- 声明当游标遍历完后将标志变量置成某个值

DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1

-- 打开游标

open report

-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致

fetch report into r_id

-- 当s不等于1，也就是未遍历完时，会一直循环

while s<>1 do

-- 执行业务逻辑

UPDATE wh_csyx . dy_pres30207800013_001_local a

JOIN ReportHotLine b ON a.gdId = b.ID

AND b.createtime = ( SELECT max( createtime ) FROM ReportHotLine WHERE ID = r_id )

SET blms = b.banliInfo where a.gdId =r_id

-- 将游标中的值再赋值给变量，供下次循环使用

fetch report into r_id

-- 当s等于1时表明遍历以完成，退出循环

end while

-- 关闭游标

close report

END

call init_reportUrl()

mysql 3张表关联批量更新：

mysql更新语句很简单，更新一条数据的某个字段，一般这样写：

代码如下:

UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value'

如果更新同一字段为同一个值，mysql也很简单，修改下where即可：

代码如下:

UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values')

这里注意 ‘other_values' 是一个逗号（，）分隔的字符串，如：1,2,3

---