SQLTeam.com | Weblogs | Forums

How to update table

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

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

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

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

thank you very much