17,086
社区成员
发帖
与我相关
我的任务
分享
DECLARE
CURSOR csr_data IS
SELECT *
FROM gltpid;
BEGIN
FOR a IN csr_data
LOOP
UPDATE dzdagxb b
SET
b.jsydid = a.id
WHERE b.wjlj = substr(b.wjlj,instr(b.wjlj,'/',1,1),instr(b.wjlj,'/',1,5)+instr(b.wjlj,'/',1,1)-2)
and
a.dah = b.tdzh ;
END LOOP;
END;
update b
set b.jsydid =
(select a.id
from a
where a.tdzh = b.tdzh
and instr(b.wjlj, a.wjlj) = 1)
where exists (select 1
from a
where a.tdzh = b.tdzh
and instr(b.wjlj, a.wjlj) = 1);
上面的代码有错误,使用这个
update b
set b.jsydid =
(select a.id
from a
where a.tdzh = b.tdzh
and instr(b.wjlj, a.wjlj)=1 --替换掉了 a.wjlj = substr(b.wjlj,instr(b.wjlj,'/',1,1),instr(b.wjlj,'/',1,5)+instr(b.wjlj,'/',1,1)-2)
and
)
where exists (select 1
from a
where a.tdzh = b.tdzh
and instr(b.wjlj, a.wjlj)=1
);
--为啥要用游标呢?
--思路是 使用update join 替换掉游标
update b
set b.jsydid =
(select a.id
from a
where a.tdzh = b.tdzh
and instr(b.wjlj, a.wjlj) --替换掉了 a.wjlj = substr(b.wjlj,instr(b.wjlj,'/',1,1),instr(b.wjlj,'/',1,5)+instr(b.wjlj,'/',1,1)-2)
and
)
where exists (select 1
from a
where a.tdzh = b.tdzh
and instr(b.wjlj, a.wjlj)
);