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

ahmed salah 3,216 Reputation points

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)

create table #pldata
zplid int,
Status nvarchar(50)
insert into #pldata(zplid,Status)

expected result

zplid Status
2050 NULL
2090 have Correct Flag
2095 have Correct Flag
2080 have Correct Flag
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,045 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 113.7K Reputation points

    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)
    0 comments No comments

0 additional answers

Sort by: Most helpful