update tProds set InitialRemainder = isnull((select sum(TankStat.InitialRemainder) 
from TankStat 
where tProds.ProductCode = TankStat.ProductCode and TankStat.RID = (select FirstRID from tRids where tRids.AfsID = TankStat.AfsID)), 0 );

  update #Prods set InitialRemainder = T1.InitialRemainder
  from #Prods P1 inner   join 
  (select P.afsid, p.productCode, ISNULL(sum(T.InitialRemainder),  0 ) as InitialRemainder
  from #Prods P inner   join TankStat T on P.afsID = T.afsID and P.ProductCode = T.Productcode
                              inner join #Rids R on T.RID = R.FirstRID       
  group by P.afsid, p.productCode) as T1 on P1.afsID = T1.afsID and P1.ProductCode = T1.Productcode