declare @t table(id int,col_1 int,col_2 int)
insert into @t select 1,30,0
insert into @t select 2,20,0
insert into @t select 3,60,0
insert into @t select 4,40,0



declare @num int
set @num=100
update @t set col_2=(case when a.id<c.id then a.col_1 else @num-(select sum(col_1) from @t where id<c.id) end) 
from @t a,
(select top 1 * from (select id,cnum=(select sum(col_1) from @t where id<=a.id) from @t a)b where cnum>=@num order by cnum)c
where a.id<=c.id


select * from @t

declare @t table(id int identity,c1 int,c2 int) 
insert @t(c1) select 30 
insert @t(c1) select 20 
insert @t(c1) select 60 


declare @i int  
set @i=100  
update @t set c2 = case when @i-c1>=0 then c1 else c1+@i end,@i = @i - c1 
select * from @t 

/*
id          c1          c2
----------- ----------- -----------
1           30          30
2           20          20
3           60          50

(3 row(s) affected)
*/

if object_id('ta') is not null
drop table ta
create table ta (ID int,col_1 int,col_2 int)
insert into ta
select 1,30,null union all
select 2,20,null union all
select 3,40,null

if object_id('tb') is not null 
drop table tb
create table tb (s_ID varchar(10),col_1 int)
insert into tb
select '1',100 union all
select '2,3',50 union all
select '8',300 union all
select '9',400


update a
set col_2 = case when (select sum(col_1) from ta where id <= a.id and 
                      charindex(','+ltrim(id)+',',+','+(select s_id from tb where charindex(','+ltrim(a.id)+',',','+s_id+',') > 0)+',')> 0)
                      < (select col_1 from tb where charindex(','+ltrim(a.id)+',',','+s_id+',') > 0)
                 then col_1 
                 else (select col_1 from tb where charindex(','+ltrim(a.id)+',',','+s_id+',') > 0) -
                      (select sum(col_1) from ta where id <= a.id and 
                      charindex(','+ltrim(id)+',',+','+(select s_id from tb where charindex(','+ltrim(a.id)+',',','+s_id+',') > 0)+',')> 0)+col_1
                 end
from ta a 

select * from ta
/*
ID          col_1       col_2   
----------- ----------- --------------
1           30          30
2           20          20
3           40          30

（所影响的行数为 3 行）
*/

create table t1(Id int,col_1 int,col_2 int)
insert into t1(id,col_1) select 1,30
insert into t1(id,col_1) select 2,20
insert into t1(id,col_1) select 3,40

create table t2(Id varchar(10),col_1 int)
insert into t2(id,col_1) select 1,100
insert into t2(id,col_1) select '2,3',50
insert into t2(id,col_1) select 8,300
insert into t2(id,col_1) select 9,400

with cet 
as
(
select a.*,b.col_1 as b_col_1 ,num=row_number() over(partition by b.id order by a.id) from t1 a,t2 b
where charindex(','+ltrim(a.id)+',',','+b.id+',')>0
)

select a.id,a.col_1 ,col_2= case when (select sum(b_col_1)- sum(col_1) from cet where a.b_col_1=b_col_1  and a.num-1>=num) is null then a.col_1
else (select sum(b_col_1)- sum(col_1) from cet where a.b_col_1=b_col_1  and a.num-1>=num) end
from cet a

/*
id          col_1       col_2
----------- ----------- -----------
1           30          30
2           20          20
3           40          30

(3 行受影响)

*/

--修正一下
create table t1(Id int,col_1 int,col_2 int)
insert into t1(id,col_1) select 1,30
insert into t1(id,col_1) select 2,20
insert into t1(id,col_1) select 3,40

create table t2(Id varchar(10),col_1 int)
insert into t2(id,col_1) select 1,100
insert into t2(id,col_1) select '2,3',50
insert into t2(id,col_1) select 8,300
insert into t2(id,col_1) select 9,400

with cet 
as
(
select a.*,b.col_1 as b_col_1 ,num=row_number() over(partition by b.id order by a.id) from t1 a,t2 b
where charindex(','+ltrim(a.id)+',',','+b.id+',')>0
)

select a.id,a.col_1 ,col_2= case when (select isnull((sum(b_col_1)- sum(col_1)),0) from cet where a.b_col_1=b_col_1  and a.num-1>=num) >=0 then a.col_1
else (select isnull((sum(b_col_1)- sum(col_1)),0) from cet where a.b_col_1=b_col_1  and a.num-1>=num) end
from cet a

/*
id          col_1       col_2
----------- ----------- -----------
1           30          30
2           20          20
3           40          30

(3 行受影响)

*/