Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JasonSQL
Starting Member
8 Posts |
 Posted - 2012-01-18 : 12:57:44
|
| HiI have 2 sets of user account informationI would like to update the table1.email_address field with the table2.primary_email field based on their being a match of table1. email_address in the table2.all_user_emails_history tableThe table2. all_user_emails_history contains all the email addresses the user ever hadI'm using a LIKE to join the 2 tables:ON table2.all_user_emails_history LIKE '%' + table1.email_address + '%'I think that there is an issue with the like as it appears to be updating when email addresses are similar. e.g. anne.smith@domain1.com matches dianne.smith@domain1.comUnfortunately there are no spaces in the table2.all_user_emails_history, but the email address are preceded with an 'smtp:'. Maybe that could help?e.g.smtp:Dianne.smith@domain1.com;SMTP:Dianne.smith@domain2.com;Is there a better way to reliably link these 2 tablesThanks in advanceJason |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-01-18 : 13:20:52
|
| Email addresses should be unique. Can you use an equality operator (=) instead of the LIKE operator? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-18 : 13:44:55
|
| ON table2.all_user_emails_history LIKE '%smtp:' + table1.email_address + ';%'provided that EVERY Email address in table2.all_user_emails_history is prefixed/suffixed in that way without exception |
|
|
|
JasonSQL
Starting Member
8 Posts |
Posted - 2012-01-19 : 04:14:09
|
quote:
Originally posted by Lamprey
Email addresses should be unique. Can you use an equality operator (=) instead of the LIKE operator?
The email addresses in the table2.all_user_emails_history contain lots of other email addresses so that one won't work i'm afraid |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-19 : 04:27:44
|
quote:
Originally posted by JasonSQL
The email addresses in the table2.all_user_emails_history contain lots of other email addresses so that one won't work i'm afraid
Did you try my suggestion? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-01-19 : 04:48:06
|
| A better idea would be to do some work to unlink your history table into a row for each email address for each person.If the emails are in a delimited list separated by the semi colon character then this will be pretty easy.Using a table valued function you could do it with one CROSS APPLY statement.You have identified a pain point. Don't just code around it. Remove the pain.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
JasonSQL
Starting Member
8 Posts |
Posted - 2012-01-20 : 09:14:39
|
quote:
Originally posted by Kristen
quote:
Originally posted by JasonSQL
The email addresses in the table2.all_user_emails_history contain lots of other email addresses so that one won't work i'm afraid
Did you try my suggestion?
HiYes I did, it returned 0 results |
|
|
|
JasonSQL
Starting Member
8 Posts |
Posted - 2012-01-20 : 09:17:13
|
quote:
Originally posted by Kristen
ON table2.all_user_emails_history LIKE '%smtp:' + table1.email_address + ';%'provided that EVERY Email address in table2.all_user_emails_history is prefixed/suffixed in that way without exception
That worked really well thank youVery much appreciated |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-20 : 09:27:35
|
Well you can't have it both ways! but if its working now that's fine  T.C. is right though - you need to SPLIT that data into a Child Table, and NOT have it stored as a concatenated list. |
|
|
|
|
|
|
Running an update on a LIKE join