0

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 |johndoe@test.com
2 |janedoe |janedoe@test.com
3 |otherguy|otherguy@test.com

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 ?

Dai
  • 141,631
  • 28
  • 261
  • 374
user3223850
  • 7
  • 2
  • 1
    `from` is invalid in standard SQL for an `update` statement. Which DBMS are you using? –  Sep 18 '17 at 08:43
  • And this is why the data should be normalised and not store multiple values in a single column (of a row). – Damien_The_Unbeliever Sep 18 '17 at 08:45
  • What exactly do you need to replace with what? Can you show us what you'd like the result to be? Also storing multiple independant values in a single column separated by a ; is terrible db design – Magisch Sep 18 '17 at 08:50
  • I need all the emails within the same field, because I need to use this field to send a notification email. So the results should be 1| johndoe@test.com; janedoe@test.com 2| otherguy@test.com; johndoe@test.com – user3223850 Sep 18 '17 at 08:55
  • 1
    The fact that you need to concatenate those values together when using them somewhere else is **not** a good reason for storing those values pre-concatenated together within the database. – Damien_The_Unbeliever Sep 18 '17 at 08:56
  • Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? –  Sep 18 '17 at 09:04
  • A solution could be found by writing a stored procedure. You would need to iterate over the existing user ids, for a given record, and build up a list of email addresses to replace the original value with. This cannot be achieved in a simple query. Alternatively, if you change the schema, so that table 2 looks something like: `ID, NotificationID, UserID`, then it would be fairly straight-forward to do a query which returned all email addresses for a given notification mail-out. – Peter Abolins Sep 18 '17 at 09:29

2 Answers2

1

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

Brett
  • 1,540
  • 9
  • 13
0

I guess you need the following

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

demo

Radim Bača
  • 10,646
  • 1
  • 19
  • 33