CODEWITHSUNDEEP
sqlsql-server
PocketSpider
PocketSpider

Reputation: 139

Inserting value into column based on column in separate table

I currently have a situation where I will have 2 tables:

+--------------+-----------------+-----------------+-----------------+
| OriginalID   |   NewID         |   FirstName     |   lastName      |
+--------------+-----------------+-----------------+-----------------+
|     123456   |                 |     billy       |      bob        |
|     234567   |                 |     tommy       |      smith      |
|     987654   |                 |     sarah       |      anders     |
+--------------+-----------------+-----------------+-----------------+ etc etc

and

+--------------+-----------------+
| OriginalID   |   NewID         |
+--------------+-----------------+
|     123456   |  1111111        |
|     234567   |  1111112        |
|     987654   |  1111113        |
+--------------+-----------------+

Without going in-depth into the process itself, I take a record from the first table and insert it into a different system, which gives a record in the form of the second table (generates a custom ID for it).

What I want to do is for every record in the second table, take the NewID and place it into the row with the same OriginalID in the first table (so that it looks like this:

+--------------+-----------------+-----------------+-----------------+
| OriginalID   |   NewID         |   FirstName     |   lastName      |
+--------------+-----------------+-----------------+-----------------+
|     123456   |  1111111        |     billy       |      bob        |
+--------------+-----------------+-----------------+-----------------+

As a note, the only values I care about dealing with are the OriginalID and NewID, none of the other values are needed. This will happen for multiple tables with different names, so I have given a generic example. For this example the tables can be called 

ContactRecords (first table) and NewContact (second table)

I have read over several examples on SO about this type of problem, but none of them quite fit the solution I'm looking for.

Thanks in advance!

Upvotes: 0

Views: 34

Answers (1)

FLICKER
FLICKER

Reputation: 6683

This looks like a join update which we had here many times.

update old
set NewID = new.NewID
from ContactRecords as old
  inner join NewContact as New on new.OriginalID = old.OriginalID

Upvotes: 1

Related Questions