How to Update status for pl data table when at lease one row part level = 1 for zplid ?

Question

I work on sql server 2012 I have issue I need to update status for table pl data where i have at least partlevel=1
for every zplid ?

meaning I need to update status for historypl table by message 'have Correct Flag'

when it have only one row have partlevel=1 for zplid .

if zplid have only one row partlevel=1 on history pl table then update status to 'have Correct Flag' on table pl data

so how to do update statment ?

what i try :

update d set d.status='have Correct Flag' from #pldata d

create table #historypl
(
zplid   int,
partlevel int
)
insert into #historypl(zplid,partlevel)
values
(2050,0),
(2050,0),
(2050,0),
(2090,0),
(2090,0),
(2090,1),
(2095,0),
(2095,1),
(2080,1)

create table #pldata
(
zplid int,
Status nvarchar(50)
)
insert into #pldata(zplid,Status)
values
(2050,NULL),
(2090,NULL),
(2095,NULL),
(2080,NULL)

expected result

zplid Status
2050 NULL
2090 have Correct Flag
2095 have Correct Flag
2080 have Correct Flag

Answer 1

Check one of solutions:

update #pldata
set status = 'have Correct Flag' 
where zplid in (select zplid from #historypl where partlevel = 1 group by zplid having count(*) = 1)