update t2 set t2.famount= t2.fprice*t2.FQty
from ICStockBill t1
inner join ICStockBillEntry t2 on t1.FInterID=t2.FInterID
where t1.FTranType=29 and
CONVERT(CHAR(10),t1.FDate,21)>='2011-09-01' AND
CONVERT(CHAR(10),t1.FDate,21)<='2011-09-30'
以上是我的更新语句。不知道为什么更新非常慢。要等很久很久。。
按以上条件查询出来的记录只有80条
PS:FAmount 数据类型为 decimal(20,2)
FPrice,FQty的数据类型为 decimal(28,10)
--如果t1.FDate是datetime类型的话这样试试
--可以在FDate和FTranType上建个联合索引
update t2 set famount= fprice*FQty
from ICStockBill t1 join ICStockBillEntry t2 on (t1.FInterID=t2.FInterID)
where t1.FTranType=29 and
t1.FDate>='2011-9-1' AND t1.FDate<'2011-10-1'
update t2 set t2.famount= t2.fprice*t2.FQty
from ICStockBill t1
inner join ICStockBillEntry t2 on t1.FInterID=t2.FInterID
where t1.FTranType=29 and
CONVERT(VARCHAR(10),t1.FDate,120)>='2011-09-01' AND
CONVERT(VARCHAR(10),t1.FDate,120)<='2011-09-30'
相同数据类型,你是指 fprice 和 qty 数据类型不是数值型么?转换下看看。
inner join ICStockBillEntry t2 on t1.FInterID=t2.FInterID
where t1.FTranType=29 and
CONVERT(VARCHAR(10),t1.FDate,120)>='2011-09-01' AND
CONVERT(VARCHAR(10),t1.FDate,120)<='2011-09-30'
试试这个
update t2 set t2.famount=round( t2.fprice*t2.FQty,2)
from ICStockBill t1
where t1.FInterID in (select FInterID from
ICStockBillEntry t1 where t1.FTranType=29 and
CONVERT(CHAR(10),t1.FDate,21)>='2011-09-01' AND
CONVERT(CHAR(10),t1.FDate,21)<='2011-09-30')