How to update table

Transact-SQL
,
1

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
2

Is this real life problem, class room home work or test?

Also what have you tried so far to solve this problem?

3

hi

please click arrow to the left for DROP CREATE Data 
drop table chemical
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')

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

SELECT 
          b.chemicalId
	   ,  case when count(distinct b.chemicalStatus) > 1 then 'chemical id have multiple status' else null end 
FROM 
     statusvalues a 
	     join  
     chemical b 
	       on a.chemicalid = b.chemicalId
GROUP by
   b.chemicalId

image

4

thank you very much