Last post Sep 03, 2020 07:22 AM by YihuiSun
Sep 03, 2020 01:06 AM|ahmedbarbary|LINK
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
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
insert into #chemical(chemicalId,PartId,chemicalStatus)
create table #statusvalues
insert into #statusvalues(chemicalid)
Expected result :
1241 chemical id have multiple status
Sep 03, 2020 07:22 AM|YihuiSun|LINK
According to your needs, I wrote the sql statement, you can refer to it.
insert into statusvalues(chemicalid,status)
select c1.chemicalId,iif(c1.statuscount<2, NULL, 'chemical id have multiple status')
(select c.chemicalId,count(distinct c.chemicalStatus) as statuscount
from chemical c
group by c.chemicalId) as c1
Here is the result.