Query Help: Replace Between two tables for each row

• pchad2002

  SSC Journeyman

  Points: 92

  More actions

  February 26, 2004 at 9:37 am

  #178227

  I was hoping that someone could help me figure out the appropriate query for the following situation.  I have two tables, in two different datbases. For each row in table A, I need to replace a value in table A with the a value from a corresponding row in table B. There is a one-to-one key mapping between both tables.  In other words they look something like this:

  Table A:

  RecId (pk)

  valueToBeReplaced

   

  Table B:

  RecId (fk)

  someValue.

  There are 845 records in each table. My sense is that I will need a while statement that loops through each row in B, retrieves the the value and updates the correspoding row in Table A with that value.  Problem is I do not know who to code that in SQL. 

  Any help would be greatly appreciated.

  Thanks,

  Chad

  pchad2002@hotmail.com

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738683

  More actions

  February 26, 2004 at 9:41 am

  #495896

  To see the matching values:

   

  select a.pk

      , a.valuetobereplaced

      , b.somevalue

      , b.pk

   from tableA a

       inner join db2.dbo.tableB b

           on a.pk = b.pk

  To perform the update:

  update a

      set a.valuetobereplaced = b.somevalue

   from tableA a

       inner join db2.dbo.tableB b

           on a.pk = b.pk

  Assuming you are running from dbA and have correct access rights.