根据条件进行表的更新,操作在两个表进行
UPDATE
MES.TEDAD010 B
SET
CONVERT_FLG='1',
OperateDT1 = PEDAB009_GET_OPERATE_DT(Spare1_01, Device_ID1),
Floor_CD1 = pedab009_get_floor_cd(Device_ID1),
Organization_CD1 = pedab009_get_organization_cd(Device_ID1)
WHERE
EXISTS
(
SELECT 1
FROM TEDAM010 A
WHERE
A.KEY3(+) = B.Device_ID1 AND
A.JOB_CD = 'EDA' AND
A.KEY1 = '100' AND
A.KEY2 = '110' AND
B.CONVERT_FLG='0'
)
AND CONVERT_FLG='0';
其中PEDAB009_GET_OPERATE_DT,pedab009_get_floor_cd,pedab009_get_organization_cd都是函数pedab009_get_floor_cd的内容(其他两个函数也类似):
SELECT
BODY2
INTO
RET_FLOOR_CD
FROM
TEDAM010
WHERE
JOB_CD = 'EDA' AND
KEY1 = '100' AND
KEY2 = '110' AND
KEY3 = V_DEVICE_ID; 如果TEDAD010表有5000条数据的话,用时8秒左右,有什莫办法提高效率吗
UPDATE
MES.TEDAD010 B
SET
CONVERT_FLG='1',
OperateDT1 = PEDAB009_GET_OPERATE_DT(Spare1_01, Device_ID1),
Floor_CD1 = pedab009_get_floor_cd(Device_ID1),
Organization_CD1 = pedab009_get_organization_cd(Device_ID1)
WHERE
EXISTS
(
SELECT 1
FROM TEDAM010 A
WHERE
A.KEY3(+) = B.Device_ID1 AND
A.JOB_CD = 'EDA' AND
A.KEY1 = '100' AND
A.KEY2 = '110' AND
B.CONVERT_FLG='0'
)
AND CONVERT_FLG='0';
其中PEDAB009_GET_OPERATE_DT,pedab009_get_floor_cd,pedab009_get_organization_cd都是函数pedab009_get_floor_cd的内容(其他两个函数也类似):
SELECT
BODY2
INTO
RET_FLOOR_CD
FROM
TEDAM010
WHERE
JOB_CD = 'EDA' AND
KEY1 = '100' AND
KEY2 = '110' AND
KEY3 = V_DEVICE_ID; 如果TEDAD010表有5000条数据的话,用时8秒左右,有什莫办法提高效率吗
create table t1(f1 int, f2 int,f3 int);
create table t2(f1 int, f2 int,f3 int);insert into t1 values(1,2,2);
insert into t1 values(2,3,3);
insert into t2 values(1,3,5);
insert into t2 values(3,4,6);
update t1 set (f2,f3)=(select f2,f3 from t2 where f1=t1.f1)
where exists(select 1 from t2 where f1=t1.f1);
select * from t1;drop table t1;
drop table t2;
/*
F1 F2 F3
1 3 5
2 3 3
*/