When I run your code for product 101 I get an error reported
Quote:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
That is because you have multiple sales for that product, but no date or time criteria - and no way to identify "the most recent record". You either need to be able to define exactly which sales record you want to adjust the stock by OR just do them all at once as a set. Either way you are probably going to need a datetime column in your table.
You then want to sum all the relevant records by Product Code. E.g.
declare @stock table (product_code int, [name] varchar(50), stock int)
insert into @stock (product_code, [name],stock) values
(101,'Mobile',50),
(102,'Laptop',50),
(103,'Monitor',30);
declare @sell_table table (product_code int, [name] varchar(50), sell_qty int)
insert into @sell_table (product_code, [name],sell_qty) values
(101,'Mobile', 2),
(102,'Laptop', 3),
(103,'Monitor', 5),
(101,'Mobile' , 4),
(103,'Laptop' , 2),
(101,'Mobile' , 4);
update a set a.stock = a.stock - b.sell_qty
from @stock a
inner join (select product_code, sum(sell_qty) as sell_qty from @sell_table group by product_code) b on a.product_code = b.product_code
select * from @stock
EDIT after OP comment:
Scenario 1 : You have a job that runs at the end of the day that calculates the end-of-day stock situation. So you want to total the amounts by product for the entire day
update a set a.stock = a.stock - b.sell_qty
from @stock a
inner join (
select product_code, sum(sell_qty) as sell_qty
from @sell_table
where [date] between @startOfDay and @endOfDay
group by product_code) b on a.product_code = b.product_code
But that tends to problems - you have to schedule the job, make sure it is not run twice etc.
Scenario 2 : You update the stock at the time of updating the sale.
BEGIN TRANSACTION Sale
insert into @sell_table (product_code, [name],sell_qty,[date]) values
(101,'Mobile', 2,'2021-10-27 06:59:36');
update @stock set stock = stock - 2 where product_code = 101;
COMMIT Sale;
But for that you should get into transactions etc.
Scenario 3: What I think this exercise is really about - Running Totals using
Window Functions[
^] e.g. something like this
select s.product_code, s.[name], sell_qty, [date], stock, stock - sum(sell_qty) over (partition by s.product_code order by [date])
from @sell_table st
join @stock s on st.product_code = s.product_code
order by [date]
As an aside, looking at this again I need to advise you that you should not have [name] in both stock and sell tables. That duplication is not necessary and does not follow
Database normalization[
^] principles