How to write update statement to table statusvalues where chemical id have more than one chemical status ?

Question

How to write update statement to table statusvalues where chemical id have more than one chemical status ?

I work on SQL server 2012 I face issue ; I can't update status on table statusvalues where chemical id have more than one status

as example
1241 must update status "chemical id have multiple status" because chemicalid have 2 status Rohs and china
1600 not update status because it have only one status as LifeCycle .

create table #chemical
(
chemicalId int,
PartId int,
chemicalStatus nvarchar(50)
)
insert into #chemical(chemicalId,PartId,chemicalStatus)
values 
(1241, 2250,'Rohs'),
(1241, 2700,'Rohs'),
(1241, 2900,'China'),
(1600, 2950,'Lifecycle'),
(1600, 3000,'Lifecycle')

create table #statusvalues
(
chemicalid int,
status nvarchar(50)
)
insert into #statusvalues(chemicalid)
 values
 (1241),
 (1600)

Expected result :

chemicalid status
1241 chemical id have multiple status
1600 NULL

Answer 1

Also check an approach that does not determine explicitly the unneeded counts:

update d
set [status] = case when s2 is null then s1 else 'chemical id have multiple status' end
from (
   select v.[status], v.chemicalid, c1.chemicalStatus s1, c2.chemicalStatus s2
   from #statusvalues v
   inner join #chemical c1 on c1.chemicalId = v.chemicalid
   left  join #chemical c2 on c2.chemicalId = c1.chemicalId and c2.chemicalStatus <> c1.chemicalStatus
) d

Answer 2

;with mycte as 
 (
 select chemicalId,count(distinct chemicalStatus) cnt from #chemical
 group by chemicalId)

 Merge  #statusvalues tgt 
 using mycte src on tgt.chemicalId = src.chemicalId
 When matched  and src.cnt>1  then
 Update
 set tgt.status= 'The chemical id has multiple status.' ;  

Answer 3

Hi @ahmed salah ,

Please refer below:

;with cte as (  
 select chemicalId,count(distinct chemicalStatus) count from #chemical  
 group by chemicalId)  
      
 update a   
 set a.status= case when count>1 then 'chemical id have multiple status' else NULL end  
 from #statusvalues a   
 left join cte b on a.chemicalid=b.chemicalId  
      
 select * from #statusvalues  

Output:
chemicalid status
1241 chemical id have multiple status
1600 NULL

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.