How to update comment with statment havemulti status where i have more

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

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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar5y ago
Hi Ahemad,

Please use this script,
update #parts set Comment='Have MultiStatus' where PartId not in (
select PartId from #parts where Status in ('Inside','Outside') group by PartId, Status having count(partid)>1
union
select PartId from #parts  group by PartId having count(partid)=1
)