Filling in missing data from one table to the other

• Rog Saber

  SSCrazy

  Points: 2548

  More actions

  September 16, 2010 at 9:07 am

  #227993

  I need to populate a table's missing values with values from another table. Table USER_CUST should have a value for user_id and cust_no that exists in USERS_APPLS. For example, bob has a cust_no of 'ACME' in USERS_APPLS. He should also have that value in USER_CUST. I know there's an easy way to do this - could someone help me out please?

  USER_CUST

  user_id....cust_no

  joe........ACME

  bob........ACME_TEST

  henry......ACE

  USERS_APPLS

  user_id....cust_no....app_name

  joe........ACME.......maintapp

  joe........ACME_TEST..maintapp

  joe........ACME_TEST..testapp

  bob........ACME.......maintapp

  bob........ACME_TEST..maintapp

  henry......ACE........testapp

  henry......ACME.......maintapp

  Desired Results:

  USER_CUST

  user_id....cust_no

  joe........ACME

  joe........ACME_TEST

  bob........ACME

  bob........ACME_TEST

  henry......ACME

  henry......ACE

• Russel Loski

  SSCertifiable

  Points: 7059

  More actions

  September 16, 2010 at 10:08 am

  #1222324

  Try

  Insert into User_Cust (user_id, cust_no)

  select distinct user_id, cust_no

  from USERS_APPLS left join User_Cust

  on USERS_APPLS.user_id = User_Cust.user_id

  and USERS_APPLS.cust_no = User_Cust.cust_no

  where User_Cust.user_id is null

  Russel Loski, MCSE Business Intelligence, Data Platform