数据量太大,更新操作无法进行,有没有更好的办法?

today416

如下的更新语句,表bajiaohmxxg 有几万的记录,甚至上十万,条件里的定单编号 也有几万.这个一更新就死机,有每哟更好的办法啊

update bajiaohmxxg set 已核销=-1 where 订单批号 in (select distinct 订单批号 from cxaxiangd)

zyp

update bajiaohmxxg set 已核销=-1 where 订单批号 in (select distinct 订单批号 from cxaxiangd) AND 已核销<>-1

todaynew

today416 发表于 2011-8-10 09:13
如下的更新语句,表bajiaohmxxg 有几万的记录,甚至上十万,条件里的定单编号 也有几万.这个一更新就死机,有每 ...

ｓｑｌ的in子句会使得操作过程在子程序的记录中前后找一通，如果子程序的记录很多的话，当然就会很慢。为了避免这种胡乱的遍历行为，可以用ADO或DAO记录集解决问题。

dim rs1 as new adodb.recordset,ssql1 as string
dim rs2 as new adodb.recordset,ssql2 as string
dim i as long,j as long
ssql1="select distinct 订单批号 from cxaxiangd"
rs1.Open ssql1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
for i=1 to rs1.recordcount
　　ssql2="select * from bajiaohmxxg where 订单批号='" & rs1!订单批号.value & "'"
　　rs2.Open ssql2, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
　　for j=1 to rs2.recordcount
　　　　rs2!已核销.value=-1
　　　　rs2.update
　　　　rs2.movenext
　　next
　　rs1.movenext
　　rs2.close
next
rs1.close
set rs1=nothing
set rs2=nothing

或者
dim rs as new adodb.recordset
dim ssql as string
dim i as long
ssql="select distinct 订单批号 from cxaxiangd"
rs.Open ssql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
for i=1 to rs.recordcount
    ssql="update bajiaohmxxg set 已核销=-1 where 订单批号='" & rs!订单批号.value & "'"
    currentdb.execute ssql
    rs.movenext
next
rs.close
set rs=nothing

roych

貌似可以用内联接来做更新查询吧？