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
3个 UPDATE比开游标划算:
举其中一种情况的例子:
UPDATE BBBSET BBB.DIQUHAO = (SELECT ABB.DIQUHAO FROM ABB WHERE ABB.ID = BBB.ID)
WHERE EXISTS(SELECT 1
FROM ABB
WHERE BBB.ID = ABB.ID
AND ABB.XINGMING = BBB.XINGMING
AND ABB.YIBAOHAO = BBB.YIBAOHAO)
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)