How to write update statement to table statusvalues where chemical id have more

Question

How to write update statement to table statusvalues where chemical id have more

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
September 3, 2020 at 1:13 am

#3785807
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)

 select * from #statusvalues
```
Expected result :
chemicalid status
1241 chemical id have multiple status
1600 NULL

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

;with chem_count as (
    select chemicalId
    from #chemical
    group by chemicalId
    having count(distinct chemicalStatus)>1)
update sv
set [status]='chemical id have multiple status'
from #statusvalues sv
     join chem_count cc on sv.chemicalId=cc.chemicalId;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 2

Think carefully about updating.

A Chemical ID tells what it is. The part number is unique, very possibly tied to a vendor. Losing the status by updating them to be the same - you will lose an attribute that you might need to see.

Say you want a certain chemical, and you see several part numbers. Would you need status ever to determine what part number to use?