数据的逐行处理

[禅院小沙弥]

create table contractinfo
(
contractid int primary key,
symbolnum varchar(10),
contractname varchar(24),
PriceTick decimal(20,8),
Price decimal(20,8)
);
 
create table qoutdata
(contractid int ,
RisePrice decimal(20,8),
FallPrice decimal(20,8),
OpenPrice decimal(20,8),
NewPrice decimal(20,8),
foreign key (contractid) references contractinfo(contractid) on delete cascade on update cascade
);

insert into contractinfo values(101,'al1305','铝1305',3,2001) ;
insert into contractinfo values(102,'au1305','金1305',5,42000) ;
insert into contractinfo values(103,'ag1305','银1305',0.3,27000) ;
insert into contractinfo values(104,'cu1305','铜1305',10,2550) ;
insert into qoutdata(contractid,openprice) values(101,2001);
insert into qoutdata(contractid,openprice) values(102,42000);
insert into qoutdata(contractid,openprice) values(103,25000);
insert into qoutdata(contractid,openprice) values(104,2550);

update qoutdata set RisePrice=1.1*openPrice,newPrice=openPrice,FallPrice=0.9*openPrice;
-----------------------------------------------------------------------
后面的SQL 语句将所有行都改为一样的，现在需要的是逐行更新，求指教，顺便说下原因
------------------------------------------------------------------------
USE test
GO
DECLARE @risePrice decimal(20,8); 
DECLARE @fallPrice decimal(20,8); 
DECLARE @openPrice decimal(20,8);
DECLARE @priceTick decimal(20,8); 
DECLARE @price decimal(20,8);
Declare  updcur CURSOR FOR
SELECT 
a.RisePrice,
a.fallprice,
a.openprice,
b.pricetick,
b.price 
FROM qoutdata AS a,contractinfo as b where a.contractid=b.contractid
FOR UPDATE
OPEN updcur


FETCH NEXT FROM updcur INTO @risePrice,@fallPrice,@openPrice,@priceTick,@price
WHILE @@FETCH_STATUS=0
BEGIN

IF @risePrice%@priceTick<>0 
BEGIN
SET @risePrice=@risePrice-@risePrice%@priceTick
UPDATE qoutdata Set RisePrice=@risePrice
END

IF @fallPrice%@priceTick<>0 
BEGIN
SET @fallPrice=@fallPrice-@fallPrice%@priceTick+@priceTick
UPDATE qoutdata SET FallPrice=@fallPrice
END

FETCH NEXT FROM updcur INTO @risePrice,@fallPrice,@openPrice,@priceTick,@price
END
CLOSE updcur
DEALLOCATE updcur

[發糞塗牆]

你每句update少了where条件，所以都是批量更新

[禅院小沙弥]

引用 1 楼 DBA_Huangzj 的回复:

你每句update少了where条件，所以都是批量更新

前面不是有条件吗？那我要怎么去限制呢？

[禅院小沙弥]

好了 我知道了 ，谢谢斑竹