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

ahmed salah 3,216 Reputation points
2020-12-26T19:26:19.187+00:00

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
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
Transact-SQL
Transact-SQL
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
    2020-12-26T19:33:28.333+00:00

    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