0

I'm having a hard time searching for this, and if this question has already been asked and answered I will gladly use that information.

I have a table that is basically complete. But I need to change the values of one column in that I have in a text file. The text file has paired values. One matches an existing value in another column of the table, the other is the new value I need to use to replace the old value in the table.

So for example I have stuff like this in the table:

Name        Address
John Smith  123 Main St
Jim Brown   123 Main St
Bob Jones   123 Main St

And in the text file I have:

John Smith;123 Real Address
Jim Brown;456 Another Real Address
Bob Jones;789 Yet Another Address

I want to be able to match on the name and insert each matching address into the table. Can I do this in a big query? Perhaps an update with a join to a selected set of values like this:

UPDATE MyTable
SET Name = (SELECT **all my values here**)
WHERE Name = **something**

Or maybe it would be possible to export the entire table, and merge the values in a text file with a script, then reinsert the new table values? I can't figure out a convenient way to do this though.

Jens
  • 67,715
  • 15
  • 98
  • 113
Joe M
  • 3,060
  • 3
  • 40
  • 63

1 Answers1

1

Use SSIS or any tool you prefer to import the text file to a separate table, and then UPDATE your existing table with a JOIN to the new table.

There are already plenty of questions on the site that answer the question of how to UPDATE a table with a JOIN to another table. Here is a good one:

How can I do an UPDATE statement with JOIN in SQL?

Community
  • 1
  • 1
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Yes, this was on a site I didn't have full control over, but I contacted the owner and he gave me permission to add a new table, which will make this trivially easy. – Joe M Jun 06 '16 at 16:42