Complicated SQL Update command

Hello.

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.

Table 1
This table has a column, call it ID, that consists of this string  ' Unique ID' + '@' + 'Another Unique ID'. A few examples of this are:
12067@0A34702
38012@0A56109
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.
WaldaIncAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YiogiCommented:
Not that hard.
UPDATE Database1.dbo.Table1
       SET ID = Database2.dbo.Table2.PersonID + "@" + Database2.dbo.Table2.Person2ID
       FROM Database2.dbo.Table2
            WHERE SubString(ID, 0, CHARINDEX('@')) = Database2.dbo.Table2.PersonID

Open in new window

0
brejkCommented:
USE Database1
GO

UPDATE T1
SET T1.ID = T2.Person2ID
FROM dbo.Table1 T1
INNER JOIN Database2.dbo.Table2 T2
ON LEFT(T1.ID,CHARINDEX('@',T1.ID)-1) = T2.PersonID

I assume that your databases are on the same server (instance). Also, I assume that ID column is updateable (I mean there are no foreign keys in other tables referencing ID column that can cause the update impossible).
0
WaldaIncAuthor Commented:
That didn't work.

1st I got this error message:

Msg 189, Level 15, State 1, Line 4
The charindex function requires 2 to 3 arguments.

After looking that up, I found that it wanted a second argument, a column name. So I added the ID column name and then it gave me a zero records updated.

Now I know this isn't right, becuase if I copy the first part of one of those ID's and put it in a select statment on the other table (matching it to PersonID) I get a hit.

I've tried changing your update to just a select too (just to see if I can see the data) but that blew up.

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

WaldaIncAuthor Commented:
Wait Brejk.  That message I just posted was not from your suggestion.  I will try yours next.

0
YiogiCommented:
Sorry about that. I don't have SQL Server in front of me so couldn't check it for correctness. Where did you put the ID column? Before or after the '@'?

It should be like snippet below. Also are all the columns strings (i.e. VarChars or NVarChars)?
UPDATE Database1.dbo.Table1
       SET ID = Database2.dbo.Table2.PersonID + "@" + Database2.dbo.Table2.Person2ID
       FROM Database2.dbo.Table2
            WHERE SubString(ID, 0, CHARINDEX(ID, '@')) = Database2.dbo.Table2.PersonID

Open in new window

0
YiogiCommented:
grrr. Reverse that:

UPDATE Database1.dbo.Table1
       SET ID = Database2.dbo.Table2.PersonID + "@" + Database2.dbo.Table2.Person2ID
       FROM Database2.dbo.Table2
            WHERE SubString(ID, 0, CHARINDEX('@', ID)) = Database2.dbo.Table2.PersonID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WaldaIncAuthor Commented:
Ok Brejk, this is the error I got with your code snippet.

Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.

Maybe the -1 you are trying?  Not sure, I tried some extra paranthesis, but that didnt' work.

0
YiogiCommented:
It's not a problem with parenthesis. Yes the -1 is not needed in his code so you can remove it. But to throw that exception it means you have a field looking like this
@xxxxxxx

i.e. the id on the left hand side is null. Please try my fixed code. Also if you will use Brejk's code do remember to change
SET T1.ID = T2.Person2ID
to
SET T1.ID = T2.PersonID + '@' + T2.Person2ID
0
WaldaIncAuthor Commented:
Thanks, this worked.

Also, I didn't want the column to be updated with the @, just updated with the new value. However, I just removed the + @ + ... portion and got exactly what I wanted.

Thanks for the fast response.
0
YiogiCommented:
Ok sorry I assumed you wanted to keep the original value format and simply change the first part of the string. Glad you got it resolved.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.