有a,b两张表,a表有字段tdzh,wjlj,id,b表有tdzh,wjlj,jsydid
现在要把a表的id更新到b表的jsydid上,判断条件是两张表的wjlj和tdzh,但是b表的wjlj需要截取一部分才能与a表的wjlj相等,
那现在语句怎样写比较好?以下是我写的语句,但是效率太低了
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;
现在要把a表的id更新到b表的jsydid上,判断条件是两张表的wjlj和tdzh,但是b表的wjlj需要截取一部分才能与a表的wjlj相等,
那现在语句怎样写比较好?以下是我写的语句,但是效率太低了
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;
怎么都是b的字段,考虑下如何优化这个函数吧。看看用正则表达式能不能使其简便点3.数据多试试bulk collect. 或者按条数commit.
--思路是 使用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)
);
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 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);