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

  • 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

  • ;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

  • 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?

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

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