2

I'm working in SQL Server 2012. I need to migrate data from one table to another, but I need to duplicate the data as well. Here is the setup:

Table A has a 1 to many relationship to Table B. Some of the data in each record in Table A needs to be moved to multiple records of Table B related to the Table A record. Each record in Table A needs to copy its data into multiple records of table B for any records in Table B that links to a given Table A record.

I need a way to do this with one single SQL UPDATE if at all possible. And I need to understand how the SQL works. I've not been able to find a way to do this through Google or searching SO.

Table A:
ID : int
Name : varchar2
Age : int
Type_ID : int
Rating : int

Table B:
ID : int
Table_A_ID : int
Name : varchar2
Age : int
Subject_ID : int
Note : varchar2

So in this example Age and Name need to be copied from Table A to all records of Table B that share Table_A_ID with Table A's ID field. Once the migration is successful, I will remove Name and Age from Table A.

tvCa
  • 796
  • 6
  • 13
hatch22
  • 797
  • 6
  • 18

1 Answers1

2

Updating B is straightforward, simply JOIN the two tables together, indicate which table you intend updating, and map the columns across:

UPDATE b
SET b.Age = a.Age, 
    b.Name = a.Name
FROM TableB b
INNER JOIN TableA a
ON b.Table_A_ID = a.ID;

If you need to to also add missing rows, or delete removed rows, you might instead look at MERGE instead to avoid needing to run separate INSERT, UPDATE and DELETE statements

StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • Thank you. I had not realized a JOIN could be used as part of an UPDATE. For some reason I thought that JOIN always resulted in a temporary view of the joined data, not a way to modify the actual joined tables. This has solved my problem and is Accepted. – hatch22 Dec 16 '14 at 18:34
  • Glad I could help. The ["join in update" syntax](http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) isn't a standard, and differs between the various RDBMS's – StuartLC Dec 16 '14 at 18:37