BillMaster:
BillID ProductID (BillID为自增字段,ProductID为唯一索引)
1 001
2 002
3 003
4 004BillDetail:
ID BillID StuffID
1 1 00A
2 1 00B
3 1 00C
4 2 00A
5 2 00D
6 2 00DBatchModiferStuff:
ID Guid ProductID OldStuffID NewStuffID
1 xx-xx 002 00A 00C
BillMaster和BillDetail是主从表,通过BillID字段关联,BillMaster是主表,BillDetail是明细表,
BillMaster的ProductID字段是货号的意思,BillDetail表的StuffID是物料的意思。(BillDetail是
货号的原料清单,可是他们却不是根据ProductID关联,而是通过自增字段BillID关联)
现在要批量修改原料清单(修改BillDetail),条件是根据BatchModiferStuff这张表来的,
BatchModiferStuff的ID字段为自增,Guid就是GUID了,OldStuffID是要替换掉的原料ID,
NewStuffID为要替换成新的原料ID,这句UPDATE语句要怎么写,小弟写出来的老是怪怪的,最后用了游标,
速度太慢了,
我第1次是:
update BillDetail set StuffID=c.newstuffid from BillDetail a
join BillMaster b on a.BillID = b.BillID
join BatchModiferStuff c on c.productID= b.ProductID and c.OldStuffID=a.StuffID
where b.ProductID in (select ProductID from BatchModiferStuff where guid=@GUID)
好象有问题,却不知道问题出在哪里????????
-------------------
第2次是:
create view v_BillDetail
as
SELECT b.ProductID, a.*
FROM BillDetail a INNER JOIN
BillMaster b ON b.BillID = a.BillIDupdate BillDetail set StuffID=a.OldStuffID from BatchModiferStuff a
join v_BillDetail b on b.productID= a.ProductID and b.StuffID=a.OldStuffID
where a.guid=@GUID
哇靠,这次把BillDetail的所有的StuffID都修改成同一个原料了,还好是在测试库里试了下第3次是:
declare cur_up_Woof cursor for select a.ID from v_BillDetail a where a.StuffID=@OldStuffID and a.ProductID in (select ProductID from BatchModiferStuff where Guid=@Guid)
open cur_up_Woof
fetch next from cur_up_Woof into @WoofID
while @@fetch_status = 0
begin
update BillDetail set StuffID=@NewStuffID where ID=@WoofID
fetch next from cur_up_Woof into @WoofID
end
close cur_up_Woof
deallocate cur_up_Woof问:
A:第2次为什么会把所有的StuffID都修改成同一个原料了?
B:那句UPDATE语句标准点要怎么写?
C:第1次问题出在哪里??
BillID ProductID (BillID为自增字段,ProductID为唯一索引)
1 001
2 002
3 003
4 004BillDetail:
ID BillID StuffID
1 1 00A
2 1 00B
3 1 00C
4 2 00A
5 2 00D
6 2 00DBatchModiferStuff:
ID Guid ProductID OldStuffID NewStuffID
1 xx-xx 002 00A 00C
BillMaster和BillDetail是主从表,通过BillID字段关联,BillMaster是主表,BillDetail是明细表,
BillMaster的ProductID字段是货号的意思,BillDetail表的StuffID是物料的意思。(BillDetail是
货号的原料清单,可是他们却不是根据ProductID关联,而是通过自增字段BillID关联)
现在要批量修改原料清单(修改BillDetail),条件是根据BatchModiferStuff这张表来的,
BatchModiferStuff的ID字段为自增,Guid就是GUID了,OldStuffID是要替换掉的原料ID,
NewStuffID为要替换成新的原料ID,这句UPDATE语句要怎么写,小弟写出来的老是怪怪的,最后用了游标,
速度太慢了,
我第1次是:
update BillDetail set StuffID=c.newstuffid from BillDetail a
join BillMaster b on a.BillID = b.BillID
join BatchModiferStuff c on c.productID= b.ProductID and c.OldStuffID=a.StuffID
where b.ProductID in (select ProductID from BatchModiferStuff where guid=@GUID)
好象有问题,却不知道问题出在哪里????????
-------------------
第2次是:
create view v_BillDetail
as
SELECT b.ProductID, a.*
FROM BillDetail a INNER JOIN
BillMaster b ON b.BillID = a.BillIDupdate BillDetail set StuffID=a.OldStuffID from BatchModiferStuff a
join v_BillDetail b on b.productID= a.ProductID and b.StuffID=a.OldStuffID
where a.guid=@GUID
哇靠,这次把BillDetail的所有的StuffID都修改成同一个原料了,还好是在测试库里试了下第3次是:
declare cur_up_Woof cursor for select a.ID from v_BillDetail a where a.StuffID=@OldStuffID and a.ProductID in (select ProductID from BatchModiferStuff where Guid=@Guid)
open cur_up_Woof
fetch next from cur_up_Woof into @WoofID
while @@fetch_status = 0
begin
update BillDetail set StuffID=@NewStuffID where ID=@WoofID
fetch next from cur_up_Woof into @WoofID
end
close cur_up_Woof
deallocate cur_up_Woof问:
A:第2次为什么会把所有的StuffID都修改成同一个原料了?
B:那句UPDATE语句标准点要怎么写?
C:第1次问题出在哪里??
from
BillDetail b inner join BillMaster a on b.billid=a.billid
inner join BatchModiferStuff c on b.stuffid=c.oldstuffid and a.productid=c.productid
from BillDetail b , BillMaster a , BatchModiferStuff c
where b.billid=a.billid and b.stuffid=c.oldstuffid and a.productid=c.productid
set stuffid=(select top 1 newstrffid from BatchModiferStuff where productid=a.productid and Oldstuffid=b.stuffid)
from
BillMaster a join BillDetail b on a.billid=b.billid
CrazyFor(冬眠的鼹鼠) 和 leo_lesley(leo) 的都是ok的,
你自己把问题复杂化了
From BillDetail b , BillMaster a , BatchModiferStuff c
where b.billid=a.billid and b.stuffid=c.oldstuffid and a.productid=c.productid