CODEWITHSUNDEEP
sqlinsertsas
LonelySoul
LonelySoul

Reputation: 1222

Insert into Specific Column from other table

I am trying to insert some of the values from one table to another and wondering how to make it possible.

Table A has 4 columns with A_1 and A_2 as blanks in some rows.

Table B has 3 columns with B_1 and B_2 as filled in all of them.

I want to insert values from B_1 and B_2 into A_1 and A_2 respectively where the rows are missing. I do have same id available in both of them for joining purpose.

I am thinking in line of the below

proc sql;
    insert into A
    (A_1 , A_2)
    as select B_1 , B_2
    from B
    where A_1 = '' and A_2 = ''
;
quit;

Upvotes: 0

Views: 559

Answers (2)

user2518751
user2518751

Reputation: 735

   data a;
   key = 1;
   a_1 = 4;
   a_2 = 6;
   output;
   key = 2;
   a_1 = .;
   a_2 = 6;
   output;
   key = 3;
   a_1 = 4;
   a_2 = .;
   output;
   run;

  data b;
  key = 1;
  b_1 = 14;
  b_2 = 62;
  output;
  key = 2;
  b_1 = 3;
  b_2 = 64;
  output;
  key = 3;
  b_1 = 54;
  b_2 =6 ;
  output;
  run;


    proc sql;
    create table a as
    select 
    coalesce(a_1,b_1) as a1,
    coalesce(a_2,b_2) as a2
    from a 
    left join b
    on
    (
    a.key = b.key
    );quit;

I used left join as I don't want to drop rows from a in case they are missing from b.

This proc will give you a warning:

    WARNING: This CREATE TABLE statement recursively 
    references the  target table. A consequence of
    this is a possible data integrity problem.

But from my experience, it works just fine.

Upvotes: -1

Kritner
Kritner

Reputation: 13765

I'm not familiar with SAS, and you didn't list your RBDMs, but the basic idea of the query would be:

update tableA
set a_1 = b.b_1 ,
    a_2 = b.b_2
from tableA a
inner join tableB b on a.Id = b.Id
where a.a_1 is null
   and a.a_2 is null

You have the starts of an insert statement, but unless i'm misunderstanding your scenario it sounds like you're actually looking for an update if the IDs exist between both tables.

note that this joins table a and b on an "id" field, then updates a.a_1 with b.b_1, and a.a_2 with b.b_2, only in cases where both a.a_1 and a.a_2 are null - i wasn't sure if you meant null or empty string. If you meant empty string, switch out a.a_1 is null with a.a_1 = ''

Upvotes: 2

Related Questions