CODEWITHSUNDEEP
sql
user3223850
user3223850

Reputation: 7

SQL UPDATE part of a string with value from other table

I need to replace part of a string with a value from another database table. Actually I need to replace the userids with emails.

DB1.TABLE1
ID|EMAIL
1 |johndoe; janedoe;
2 |otherguy; johndoe;

DB2.TABLE2
ID|USERID  |EMAIL
1 |johndoe |[email protected]
2 |janedoe |[email protected]
3 |otherguy|[email protected]

my query 

UPDATE
    TABLE1
set
    EMAIL = TABLE2.EMAIL
from
    DB2.TABLE2
where
    TABLE1.EMAIL = TABLE2.USERID

How can I specify the "part of the string" thing ?

Upvotes: 0

Views: 112

Answers (2)

Brett
Brett

Reputation: 1550

There are a number of comments about changing your schema...which would be the best way forward.
It looks like what you are storing in table1.email is actually a list of UserId from table2. So you'll need to break out these ids in order to join to the tables together.

If you absolutely must follow this path, then there are existing Q+As on the site that will help you:

(I've taken a leap of faith that you are using SQL server ... but if you search I'm sure you can find similar answers for other RDBMSs)

Turning a Comma Separated string into individual rows

and

Multiple rows to one comma separated value

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

I guess you need the following

UPDATE TABLE1
SET EMAIL = (
    SELECT TABLE2.EMAIL
    FROM TABLE2
    WHERE TABLE1.EMAIL LIKE TABLE2.USERID + '%');

demo

Upvotes: 0

Related Questions