Query to update values

  • can anybody help me with the query that updates the PRIMARY_DC_ID column in SHP_MANIFEST table?

    The source data is SUBORDERS.PRIMARY_DC_ID.

    If SUBORDERS.PRIMARY_DC_ID is null, then use SUBORDERS.DC_ID.

    Sample tables are like this

    SHP_MANIFEST

    Row_keySHIP_DATE_DWKEYPRIMARY_DC_ID

    15674null

    21486null

    31589null

    49456null

    SUBORDERS

    ROW_KEYPRIMARY_DC_IDDC_ID

    1null2

    211

    3null1

    422

  • --first create some tables

    create table #SHP_MANIFEST (

    Row_key int null,

    SHIP_DATE_DWKEY int null,

    PRIMARY_DC_ID int null

    )

    create table #SUBORDERS (

    ROW_KEY int null,

    PRIMARY_DC_ID int null,

    DC_ID int null

    )

    --then add your test data

    insert into #SHP_MANIFEST

    select 1, 5674, null union all

    select 2, 1486, null union all

    select 3, 1589, null union all

    select 4, 9456, null

    insert into #SUBORDERS

    select 1, null, 2 union all

    select 2,1,1 union all

    select 3, null, 1 union all

    select 4,2,2

    --assuming what the two tables have in common is the Row_key column:

    --update

    update #SHP_MANIFEST set

    PRIMARY_DC_ID = coalesce(s.PRIMARY_DC_ID , s.dc_id)

    from

    #SHP_MANIFEST m

    inner join #SUBORDERS s on s.ROW_KEY = m.Row_key

    --check

    select * from #SHP_MANIFEST

    select * from #SUBORDERS

    --clean up

    drop table #SHP_MANIFEST

    drop table #SUBORDERS

    [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]
  • It's perfect...Thanks for your help.

  • Glad I could help!

    [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

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

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