update table column based on sub table multiple value

Question

Hi,

I have a Main table and Sub table like below

create table Main (Code nvarchar(10), Amount decimal(18,2), NewAmount decimal(18,3))
create table Sub (Code nvarchar(10), Price decimal(18,2))

insert into Main values ('1111', 100,0)
insert into Sub values ('1111',10)
insert into Sub values ('1111',20)
insert into Sub values ('1111',100)

--update T Set T.NewAmount=0 from Main T inner join price P on T.code=P.Code
select * from Main
select * from Sub
drop table Main
drop table Sub

i need to update main table NewAmount column based on Sub table Price column

if Amount in Main table matched with price column in Sub Table then NewAmount in Main table should be Amount Value otherwise '0' value

Answer 1

Check a possibility:

update m
set NewAmount = isnull(s.Price, 0)
from Main m 
left join Sub s on s.Code = m.Code and s.Price = m.Amount

(If 's.Code = m.Code' is not needed, then remove it).

Another possibility can be based on "CTE".

Answer 2

Hi @kasim mohamed ,

Please also check:

update T   
set T.NewAmount=case when Amount in(select Price from Sub P where T.code=P.Code)   
then Amount else 0 end  
from Main T   

Or the cte mentioned by Viorel:

;With cte  
as(select T.*,P.Price from Main T   
   left join Sub P on P.Code = T.Code and P.Price = T.Amount)  
  
update Main  
set NewAmount = isnull(Price, 0)  
from cte  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.