现有表zz_busi2shelf_card A和表PORTVIEW@test_link B,表A的数据量非常少,表B的数据量非常大(数十万的级别)。
想要对表A的card字段进行更新,更新的值为表B的CARD_NAME字段,
连接条件为A.shelf=B.SHELF_HUM_ID and A.port=B.PORT_HUM_ID。我写的sql如下:
update zz_busi2shelf_card A set A.card=(select B.CARD_NAME from PORTVIEW@test_link Bwhere B.SHELF_HUM_ID =A.shelf and B.PORT_HUM_ID=A.port)where exists(select 1 from PORTVIEW@test_link Bwhere B.SHELF_HUM_ID=A.shelf and B.PORT_HUM_ID=A.port);可是这个sql在执行的时候非常慢,想用一个快速的方法进行更新。
且在已知表B的SHELF_HUM_ID和PORT_HUM_ID两个字段的情况下查找CARD_NAME是比较快速的。请教高手这个更新语句怎么写?是不是要用到写存储过程?
请各位大哥大姐帮忙提供解决办法,谢谢!
想要对表A的card字段进行更新,更新的值为表B的CARD_NAME字段,
连接条件为A.shelf=B.SHELF_HUM_ID and A.port=B.PORT_HUM_ID。我写的sql如下:
update zz_busi2shelf_card A set A.card=(select B.CARD_NAME from PORTVIEW@test_link Bwhere B.SHELF_HUM_ID =A.shelf and B.PORT_HUM_ID=A.port)where exists(select 1 from PORTVIEW@test_link Bwhere B.SHELF_HUM_ID=A.shelf and B.PORT_HUM_ID=A.port);可是这个sql在执行的时候非常慢,想用一个快速的方法进行更新。
且在已知表B的SHELF_HUM_ID和PORT_HUM_ID两个字段的情况下查找CARD_NAME是比较快速的。请教高手这个更新语句怎么写?是不是要用到写存储过程?
请各位大哥大姐帮忙提供解决办法,谢谢!
-- 参考以下的方法:
-- 步骤一:根据A表结构建立新A表
create table zz_busi2shelf_card_new
as
select * from zz_busi2shelf_card
where 1=2
;-- 步骤二:往新A表填充数据
insert into zz_busi2shelf_card_new
(
... -- 列名
)
select
a.... -- A表其他列
,b.card -- card 字段使用B表的
,a.... -- A表其他列
from zz_busi2shelf_card A
left join PORTVIEW@test_link B
on A.shelf=B.SHELF_HUM_ID
and A.port=B.PORT_HUM_I
;-- 步骤三:用新的A表代替A表
truncate table zz_busi2shelf_card;
insert into zz_busi2shelf_card
select * from zz_busi2shelf_card_new
;
UPDATE zz_busi2shelf_card a
SET a.card=(SELECT b.card_name
FROM (SELECT * FROM PORTVIEW@test_link WHERE SHELF_HUM_ID BETWEEN SELECT MIN(shelf) FROM zz_busi2shelf_card AND SELECT MAX(shelf) FROM zz_busi2shelf_card
AND PORT_HUM_ID BETWEEN SELECT MIN(port) FROM zz_busi2shelf_card AND SELECT MAX(port) FROM zz_busi2shelf_card
) b
WHERE B.SHELF_HUM_ID =A.shelf AND B.PORT_HUM_ID=A.port
)
WHERE EXISTS (SELECT 1
FROM (SELECT * FROM PORTVIEW@test_link WHERE SHELF_HUM_ID BETWEEN SELECT MIN(shelf) FROM zz_busi2shelf_card AND SELECT MAX(shelf) FROM zz_busi2shelf_card
AND PORT_HUM_ID BETWEEN SELECT MIN(port) FROM zz_busi2shelf_card AND SELECT MAX(port) FROM zz_busi2shelf_card
) c
WHERE c.SHELF_HUM_ID =A.shelf AND c.PORT_HUM_ID=A.port);