update table based on multiple scenario

Question

i have two table like below. if table2 cnt column is 1 then update T2 price with multiple of T1 qty
if table2 cnt column is 2 then update either of price in table2 is matching then update 'Exits'.

create table ##Table1 (code nvarchar(50), Qty float, Amount decimal(18,2), Result varchar(100))

create table ##Table1 (code nvarchar(50), Qty float, Amount decimal(18,2), Result varchar(100))
insert into ##Table1 values ('1001',1.000,145.00,'')
insert into ##Table1 values ('1001',1.000,150.00,'')
insert into ##Table1 values ('1002',1.000,220.00,'')
insert into ##Table1 values ('1003',1.000,500.00,'')
create table ##Table2 (code nvarchar(50), Price decimal(18,2), Cnt int)
insert into ##Table2 values ('1001',123.00,2)
insert into ##Table2 values ('1001',145.00,2)
insert into ##Table2 values ('1002',111.00,1)

select * from ##Table1
select * from ##Table2

i tried the below query but even the code 1001 matching with 145 price. its not updating as 'Exists'

update T1 SET T1.Result= case when (T2.Cnt=1 and T2.Code=T1.code) then cast (T2.Price * T1.Qty as nvarchar(100))
when (T2.Cnt>2 and T1.code = T2.code and (T1.Amount/T1.Qty) = T2.Price) then 'Exist' else 'NotExist' end
from ##Table1 T1 left join ##Table2 T2 on T1.Code = T2.Code

select * from ##Table1
drop table ##Table1
drop table ##Table2

thanks

Answer 1

Hi，@kasim mohamed

Welcome to Microsoft T-SQL Q&A Forum!

Please check this：

update T1   
SET T1.Result=   
case when cnt=( select cnt from ##Table2 T2   
where T2.Cnt=1 and T2.Code=T1.code)   
then cast (T2.Price * T1.Qty as nvarchar(100))  
when cnt in(select cnt from ##Table2 T2   
where T2.Cnt>=2 and (T1.Amount/T1.Qty) = T2.Price)   
then 'Exist' else 'NotExist' end  
from ##Table1 T1 left join ##Table2 T2 on T1.Code = T2.Code  

Here is result:

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.