update master table based on subtable count

Question

Hi,

I have two table like below

create table ##tab1 (code nvarchar(50), LateFlag nvarchar(10))
create table ##tab2 (code nvarchar(50), Late nvarchar(10))

insert into ##tab1 values ('1001','')
insert into ##tab1 values ('1002','')
insert into ##tab1 values ('1003','')
insert into ##tab1 values ('1004','')

insert into ##tab2 values ('1001','N')
insert into ##tab2 values ('1001','N')
insert into ##tab2 values ('1001','Y')
insert into ##tab2 values ('1001','Y')

insert into ##tab2 values ('1002','N')
insert into ##tab2 values ('1002','N')
insert into ##tab2 values ('1002','Y')

insert into ##tab2 values ('1003','Y')

insert into ##tab2 values ('1004','N')
insert into ##tab2 values ('1004','N')

select * from ##tab1
select * from ##tab2

drop table ##tab1
drop table ##tab2

i need to update ##tab1 LateFlag column based on ##tab2 Late column count (only value as 'Y')

i need result like below
create table ##Result (code nvarchar(50), LateFlag nvarchar(10))
insert into ##Result values ('1001','2')
insert into ##Result values ('1002','1')
insert into ##Result values ('1003','1')
insert into ##Result values ('1004','0')
select * from ##Result
drop table ##Result

i tried like below
select T1.Code, ISNULL(T22.Cnt,0) from ##tab1 T1 left join
(select Code, ISNULL(COUNT(*),0) as Cnt from ##tab2 T2 where T2.code='Y'
group by code) T22 on T1.code = T22.code

Thanks

Answer 1

;With cte As
(Select code, Sum(Case When Late = N'Y' Then 1 Else 0 End) As LateCount
From ##tab2
Group By code)
Update t1
Set LateFlag = c.LateCount
From ##tab1 t1
Inner Join cte c On t1.code = c.code;

Tom