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 |
|
bpod
Starting Member
3 Posts |
 Posted - 2014-01-17 : 07:46:08
|
| Hello Is this possible to do something like that: Table A contains column CosnumerLogon that stores data in the format:firstname.lastname@email.com or usernumber@email.com (e.g. 123456789@email.com)Table B contains e mail address and userNumber. I would like to update all the ConsumerLogon records in TableA in the format of 123456789@email.comto the proper e mail address from table B.Here is what I have: UPDATE A SET ConsumerLogon = (SELECT email FROM TABLE B WHERE userNumber = LEFT (A.ConsumerLogon ,CHARINDEX ( '@' , A.ConsumerLogon , 1 ) -1) )Seems like the subquery expression does not receive ConsumerLogon from the outer query.So general question is that: is this possible to refer to outer query in subquery expression? Thanks! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-01-17 : 07:50:44
|
can you post some sample data from both Table A and B ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
bpod
Starting Member
3 Posts |
Posted - 2014-01-17 : 08:25:47
|
| TABLE A CosumerLogonbart.simpson@email.com 12345@email.com TABLE B UserNumber Email 22222 bart.simpson@email.com 12345 homer.simpson@email.comI would like to update TABLE A with e mail from table Bwherever first part of CosumerLogon from table A matches UserNumber from table B. Thanks! bp |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-01-17 : 09:41:20
|
[code]update Aset ConsumerLogon = B.Emailfrom A inner join B on left(A.ConsumerLogon, charindex('@', A.ConsumerLogon) - 1) = B.UserNumber[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
bpod
Starting Member
3 Posts |
Posted - 2014-01-20 : 03:49:39
|
| Works! Thank you! |
|
|
|
|
|
|
update table using subquery with expression