I have two table (they span different databases) and I need to update a table in one with values from another. However, it is a bit more complicated than that.
This table has a column, call it ID, that consists of this string ' Unique ID' + '@' + 'Another Unique ID'. A few examples of this are:
I am only concerned with everything before the @ symbol, however I cannot count on it always being 5 characters long. As we get more, those ID's will larger.
Table 2 has two ID columns. Call them PersonID and Person2ID (made up names).
PersonID will match the number in Table 1 (everything before the @ symbol). Person2ID is what I want to write to the original ID column in Table1.
So the command would start something like this
UPDATE Table1 SET ID = [INSERT COMPLICATED WHERE CLAUSE HERE]
Let me summarize here. I want to take everything BEFORE the @ symbol in the ID column of table 1, find it's match in Table2, grab the Person2ID from that table and write it over the original ID value in Table 1.
I know this might sound crazy, however this is a bunch of legacy system stuff and the ID column I'm overwriting is no longer being used, yadda, yadda, yadda. It's a Band Aid fix, but it needs to be done to make deadlines.
Thanks in advance for any and all comments.