February 13, 2012 at 1:58 pm
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
February 13, 2012 at 2:15 pm
--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
February 14, 2012 at 3:14 pm
It's perfect...Thanks for your help.
February 14, 2012 at 5:12 pm
Glad I could help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply