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

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  February 24, 2021 at 12:47 am

  #3849281

  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

• ktflash

  Default port

  Points: 1400

  More actions

  February 24, 2021 at 5:39 am

  #3849376

  Not sure if i am grouping too many columns here but my first thought would have been this.

  Joining the table with the grouped version itself, not very efficient maybe? Guess someone will post a more effective version.

  select a.PartId
        ,a.FeatureName
        ,a.Status 
        ,Comment = case when b.counter > 1 then 'Have MultiStatus' else NULL END
  from #parts a
       inner join (select PartId
                         ,FeatureName
                         ,counter=count(distinct Status)
    from #parts
    group by PartId,FeatureName) b on a.PartId = b.PartId
      and a.FeatureName = b.FeatureName

  I want to be the very best
  Like no one ever was

  https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

• kaj

  SSCommitted

  Points: 1607

  More actions

  February 25, 2021 at 10:54 pm

  #3850310

  select
  PartId,
  FeatureName,
  Status,
  case when exists (
  select * from #parts 
  where PartId=p.PartId 
  and FeatureName=p.FeatureName 
  group by PartId, FeatureName 
  having count(distinct status) > 1
  ) 
  then 'Have Multistatus' else Comment end as Comment
  from #parts p
  order by
  PartId,
  FeatureName

  Or, since you want to update the table, perhaps something like this:

  update p
  set Comment = case when x.PartId is not null then 'Have Multistatus' else null end
  from #parts p
  outer apply (
  select PartId,FeatureName
  from #parts
  where PartId=p.PartId
  and FeatureName=p.FeatureName
  group by PartId,FeatureName
  having count(distinct status) > 1
  ) x
  where p.Comment is null and x.PartId is not null
  or p.Comment is not null and x.PartId is null