create table t(t1 int,t2 char(2),t3 int,t4 int)
insert t
select 1,'A1',20,100 union all
select 2,'A1',20,100 union all
select 3,'A2',10,50 union all
select 4,'A2',20,50 union all
select 5,'A1',10,100 union all
select 6,'A2',5,50
go
select * from t
update t
set t4 = t4 - (select sum(t3)
from t
where a.t2 = t2 and t1<= a.t1 )
from t a
select * from t