奇怪问题，求解释！

SQL code

update a set @qty = c.nowqty,     @newqty = b.totalqty - @qty,     a.nowquantity = (case when @newqty<=0 then @qty-b.totalqty                           when @newqty>0                              and a.id = (select top 1 id from #stockte where a.warehouseid = warehouseid                                              and a.cinvcode = cinvcode and a.color = color and flag = 2                                             and a.[size] = [size] order by id desc)                             and not exists (select 1 from IM_StockRecord where a.warehouseid = warehouseid                                              and a.cinvcode = cinvcode and a.color = color and recordtype = 1                                             and a.[size] = [size] and nowquantity < 0)                             then @qty-b.totalqty else 404 end) from IM_stockRecord a join #stockte b on a.id = b.id     join crt c on a.id = c.id where b.flag = 2

貌似排版有些问题？

问题在于条件判断case when这里，条件是

SQL code

a.id = (select top 1 id from #stockte where a.warehouseid = warehouseid                                              and a.cinvcode = cinvcode and a.color = color and flag = 2                                             and a.[size] = [size] order by id desc)

这里通不过，仔细看过，子查询得到的值是NULL，不加where条件得到的也是NULL，但后边表关联的条件可以通过，也就是说明#stockte是有记录和IM_StockRecord关联的，目前是用一条记录测试的，所以top 1也就是它本身，数据得到的是 404，这个条件换为 a.id = isnull((select top 1 id from #stockte order by id desc),5079) 那么结果是正确的，为何条件判断里没有过去，却在表连接那里没问题。。。

[解决办法]
update里可以对变量设置？同时变量可以设置到字段？
[解决办法]
改写为如下会不会好一点呢?

SQL code

update aset @qty = c.nowqty,    @newqty = b.totalqty - @qty,    a.nowquantity = (case when @newqty<=0 then @qty-b.totalqty                          when @newqty>0                             and d.id is not null                            and not exists (select 1 from IM_StockRecord where a.warehouseid = warehouseid                                             and a.cinvcode = cinvcode and a.color = color and recordtype = 1                                            and a.[size] = [size] and nowquantity < 0)                            then @qty-b.totalqty else 404 end)from IM_stockRecord a join #stockte b on a.id = b.idjoin crt c on a.id = c.idouter apply(select id from #stockte where a.warehouseid = warehouseid  and a.cinvcode = cinvcode and a.color = color and flag = 2 and a.[size] = [size] and a.id=id) dwhere b.flag = 2
[解决办法]
SQL code
from IM_stockRecord a join #stockte b on a.id = b.id    join crt c on a.id = c.id
[解决办法]
top 1 的字段值是null呗。
[解决办法]
我一般都是直接先select然后再对数据进行操作，方便分析．

select  
   e.nowquantity = (case ...then @qty-b.totalqty else 404 end)
from IM_stockRecord e join #stockte b on e.id = b.id
   join crt c on e.id = c.id
where b.flag = 2