Merge with a twist

• Padma-1078840

  SSC Enthusiast

  Points: 161

  More actions

  July 20, 2009 at 8:19 pm

  #208489

  Hell all,

  I have data three fields from one table that looks like this

  subbrkIdbrksubbrk

  156null

  275null

  336null

  4null23

  5null38

  6null42

  I need to extract the matching subbrkId from the table above and merge it to the table below, I am having issues because the brk and subbrk field values get repeated in the table below:

  subbrkIdbrksubbrk

  -56null

  -56null

  -56null

  -75null

  -75null

  -null23

  -null23

  -null38

  -null38

  I need to update the table with the matching subbrkId as shown below:

  subbrkIdbrksubbrk

  156null

  156null

  156null

  275null

  275null

  4null23

  4null23

  5null38

  5null38

  Any help will be MUCH appreciated! Thanks in advance

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 20, 2009 at 9:15 pm

  #1027395

  Okay, thanks for a partial setup, but please look at how I am presenting my code, including the DDL for the tables, the insert statements with the sample data. This is how you really should provide your setup for us.

  create table #TestTab1 (

  subbrkId int null,

  brk int null,

  subbrk int null

  );

  insert into #TestTab1(

  subbrkId,

  brk,

  subbrk

  )

  select 1, 56, null union all

  select 2, 75, null union all

  select 3, 36, null union all

  select 4, null, 23 union all

  select 5, null, 38 union all

  select 6, null, 42;

  -- I need to extract the matching subbrkId from the table above and merge it to the table below, I am having issues because the brk and subbrk field values get repeated in the table below:

  create table #TestTab2 (

  subbrkId int null,

  brk int null,

  subbrk int null

  );

  insert into #TestTab2 (

  brk,

  subbrk

  )

  select 56, null union all

  select 56, null union all

  select 56, null union all

  select 75, null union all

  select 75, null union all

  select null, 23 union all

  select null, 23 union all

  select null, 38 union all

  select null, 38;

  update #TestTab2 set

  subbrkId = t1.subbrkId

  from

  #TestTab2 t2

  inner join #TestTab1 t1

  on (t2.brk = t1.brk

  or t2.subbrk = t1.subbrk);

  select * from #TestTab2;

  drop table #TestTab1;

  drop table #TestTab2;

• Padma-1078840

  SSC Enthusiast

  Points: 161

  More actions

  July 21, 2009 at 11:32 am

  #1027820

  Hi Lynn,

  THAT WORKED! 🙂 Thank you very much for your help

  Next time, I will make sure I set up the presentation of questions the way you have suggested.

  Thanks again

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 21, 2009 at 11:52 am

  #1027825

  Thank you for the feedback.