没法,A表重复,只有先用以下语句: alter table tableA add myid int identity go过程: declare @id char(10) declare @qtyA int declare @item char(10) declare readA cursor for select myid,item,qty from tableA open readA fetch next from readA into @id,@item,@qtyA while @@fetch_status<>-1 begin update tableB set qty=qty-@qtyA where source_item=@item and qty>=0 if @@rowcount>0 update tableA set item=(select replace_item from tableB where source_item=@item) where myid=@id fetch next from readA into @id,@item,@qtyA end close readA deallocate readA结果:item qty myid ---------- ----------- ----------- B1 40 1 B1 50 2 B1 30 3 A1 50 4 B2 80 5 A1 10 6source_item replace_item qty ----------- ------------ ----------- A1 B1 -20 A2 B2 -30(所影响的行数为 2 行)
要提高点速度要用两游标: 1. select source_item from tableB 2. select myid,item,qty from tableA where item= -- @(tableB.source_item) 3. 第二游标运行时,当@@rowcount=0则break
我的意思是你的B表会不会出现以下数据:SOURCE_ITEM REPLACED_ITEM QTY
A1 B1 100
B1 XX 50
alter table tableA add myid int identity
go过程:
declare @id char(10)
declare @qtyA int
declare @item char(10)
declare readA cursor for
select myid,item,qty from tableA
open readA
fetch next from readA into @id,@item,@qtyA
while @@fetch_status<>-1
begin
update tableB set qty=qty-@qtyA where source_item=@item and qty>=0
if @@rowcount>0
update tableA set item=(select replace_item from tableB where source_item=@item) where myid=@id
fetch next from readA into @id,@item,@qtyA
end
close readA
deallocate readA结果:item qty myid
---------- ----------- -----------
B1 40 1
B1 50 2
B1 30 3
A1 50 4
B2 80 5
A1 10 6source_item replace_item qty
----------- ------------ -----------
A1 B1 -20
A2 B2 -30(所影响的行数为 2 行)
1.
select source_item from tableB
2.
select myid,item,qty from tableA where item= --
@(tableB.source_item)
3.
第二游标运行时,当@@rowcount=0则break
不会重复的,SOURCE_ITEM是主键。
我近期会测试一下,谢谢!
我近期会测试一下,谢谢!