update语法请教表A里有字段，id(pk,int),mno(varchar,10),louQty(float),alsoQty(float),notAlsoQty(float)
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 UPDATEQTYON AAFTER UPDATEASBEGINUPDATE OSET O.notAlsoQty=O.louQty-O.alsoQty, FORM A O,INSERTED IWHERE O.ID=I.IDENDCREATE TRIGGER UPDATECLOSETON AAFTER UPDATEASBEGINUPDATE OSET O.isClose=1 FORM A O,INSERTED IWHERE O.ID=I.ID AND O.notaLSoQty<=0ENDCREATE TRIGGER UPDATECLOSET2ON AAFTER UPDATEASBEGINUPDATE OSET O.isClose=0 FORM A O,INSERTED IWHERE O.ID=I.ID AND O.notaLSoQty>0END
我想把几个SET合在一起，要怎么写
[解决办法]
- SQL code
UPDATE OSET O.notAlsoQty=O.louQty-O.alsoQty,O.isClose= (case when O.notaLSoQty>0 then 0 else 1 end ) FORM A O,INSERTED IWHERE O.ID=I.ID