How to update comment with statment havemulti status where i have more than one status per part?

Question

User696604810 posted

I work on Sql server 2012 I face issue I can't update comment column for every part
when part have more than one status distinct inside and outside
as partid 1202 have multi status inside and outside

so i need to write query update comment where it have more than one status

distinct as part id 1202

but not update part id 1230 because it have two status outside but not

two status it is only one status but repeated

create table #parts
 (
 PartId int,
 FeatureName nvarchar(20),
 Status  nvarchar(10),
 Comment nvarchar(100)
    
 )
 insert into #parts(PartId,FeatureName,Status,comment)
 values
 (1202,'Mounting','Inside',NULL),
 (1202,'Mounting','Outside',NULL),
 (1210,'voltage','Outside',NULL),
 (1215,'voltage','Inside',NULL),
 (1220,'Mounting','Inside',NULL),
 (1220,'Mounting','Inside',NULL),
 (1230,'Mounting','Outside',NULL),
 (1230,'Mounting','Outside',NULL),
 (1285,'hold','Inside',NULL),
 (1285,'hold','Outside',NULL),
 (1300,'Heat','Inside',NULL),
 (1300,'Heat','Outside',NULL)

Expected result

 PartId    FeatureName    Status    Comment
 1202    Mounting    Inside    Have MultiStatus
 1202    Mounting    Outside    Have MultiStatus
 1210    voltage           Outside    NULL
 1215    voltage            Inside    NULL
 1220    Mounting    Inside    NULL
 1220    Mounting    Inside    NULL
 1230    Mounting    Outside    NULL
 1230    Mounting    Outside    NULL
 1285    hold                    Inside    Have MultiStatus
 1285    hold                    Outside    Have MultiStatus
 1300    Heat           Inside    Have MultiStatus
 1300    Heat           Outside    Have MultiStatus

Answer 1

User452040443 posted

Try:

update p
set p.Comment = 'Have MultiStatus'
from @Parts as p
where 
    exists
        (select 1 from @Parts as s
         where s.PartId = p.PartId and s.Status <> p.Status)

Hope this help