How to loop through table and update multiple rows

Question

hi base on the table below i have 2 table where name is sell record history and another is sell info..base on the sell info table josh have sold 100 how can i update to sell record history table

scenario:

josh package one already sold 50 how can add it up the 100 in sell info to package 1 50 add on to sold because the max cap is 100 and the balance of the sold add to package 2 of josh

table - sell Record history		
name	package	max cap	sold
Josh	1	100	0
Jack	1	100	0
Josh	2	100	0
Austin	1	100	0

table -sell info	
name	sold
Josh	150
Jack	0
Austin	0

expected output will be :

name    |package|max cap    |sold
Josh    |1      |100        |100
Jack    |1      |100        |0
Josh    |2      |100        |50
Austin  |1      |100        |0

Answer 1

No way to do it in one simple query. The query below i uses recursive CTE.

Assumption : package is numeric and starts from 1 and increment by 1 without gap

-- Create sample table
declare @sell_record table
(
    name        varchar(6),
    package     int,
    max_cap     int,
    sold        int
)

declare @sell_info table
(
    name        varchar(6),
    sold        int
)

-- Insert sample data
insert into @sell_record select 'Josh', 1, 100, 0
insert into @sell_record select 'Jack', 1, 100, 0
insert into @sell_record select 'Josh', 2, 100, 0
insert into @sell_record select 'Austin', 1, 100, 0

insert into @sell_info select 'Josh', 150
insert into @sell_info select 'Jack', 0
insert into @sell_info select 'Austin', 0

-- The query. rcte is recursive cte
; with rcte as
(
    -- anchor member. Starts with package 1
    select  i.name, r.package,
            -- allocation of sold_qty to package
            alloc    = case when i.sold >= r.max_cap - r.sold 
                            then r.max_cap - r.sold 
                            else i.sold
                            end,
            -- balance of the sold qty after allocated
            bal_sold = case when i.sold >= r.max_cap - r.sold 
                            then i.sold - r.max_cap - r.sold 
                            else 0
                            end
    from    @sell_info i
            inner join @sell_record r   on  i.name  = r.name 
    where   r.package   = 1

    union all

    -- recursive member
    select  c.name, r.package,
            -- allocation of sold_qty to package
            alloc   = case  when c.bal_sold >= r.max_cap - r.sold 
                            then r.max_cap - r.sold 
                            else c.bal_sold
                            end,
            -- balance of the sold qty after allocated
            bal_sold = case when c.bal_sold >= r.max_cap - r.sold 
                            then c.bal_sold - r.max_cap - r.sold 
                            else 0
                            end
    from    rcte c
            inner join @sell_record r   on  c.name      = r.name 
                                        and c.package   = r.package - 1
)
-- Update back sell_record
update  r
set    sold = c.alloc
from    rcte c
        inner join @sell_record r   on  c.name  = r.name
                                    and c.package   = r.package

-- show the result
select  *
from    @sell_record

/*
name    package max_cap sold
Josh    1   100 100
Jack    1   100 0
Josh    2   100 50
Austin  1   100 0
*/