日期:2014-05-17  浏览次数:20478 次

update语法请教
表A里有字段,id(pk,int),mno(varchar,10),louQty(float),alsoQty(float),notAlsoQty(float),isClose(bit)
想做一个UPDATE操作如notAlsoQty=louQty-alsoQty
如果louQty-alsoQty大于等为isClose=true,否则为false
update怎么写呀!
我现在写的是
SQL code

CREATE TRIGGER UPDATEQTY
ON A
AFTER UPDATE
AS
BEGIN
UPDATE O
SET O.notAlsoQty=O.louQty-O.alsoQty,
   FORM A O,INSERTED I
WHERE O.ID=I.ID
END
CREATE TRIGGER UPDATECLOSET
ON A
AFTER UPDATE
AS
BEGIN
UPDATE O
SET O.isClose=1
   FORM A O,INSERTED I
WHERE O.ID=I.ID AND O.notaLSoQty<=0
END
CREATE TRIGGER UPDATECLOSET2
ON A
AFTER UPDATE
AS
BEGIN
UPDATE O
SET O.isClose=0
   FORM A O,INSERTED I
WHERE O.ID=I.ID AND O.notaLSoQty>0
END


我想把几个SET合在一起,要怎么写

------解决方案--------------------
SQL code
UPDATE O
SET O.notAlsoQty=O.louQty-O.alsoQty,
O.isClose= (case when O.notaLSoQty>0
 then 0 else 1 end )
   FORM A O,INSERTED I
WHERE O.ID=I.ID