May 3, 2019 at 7:13 pm
I would like to update the EmailAddress column of a database table with emails from a list . So basically I have two lists, Emails and Username lists. I want to check each value of the UserName column against the Username list.
Then I would like to update the EmailAddress column with the email from the Email list whose position in the Email list is the same as the username position in the Username list.
I want to do something like the script below where e1, e2, e3, etc., are emails from the Email list and value1, value2, value3, etc., are usernames from the Username list.
Update Table
Set EmailAddress = (e1, e2, e3, ...)
Where UserName In (value1, value2, value3, ....)
Is it possible to do what I have mentioned above?
May 3, 2019 at 7:25 pm
Something like (make a backup of the table or wrap the update in a transaction so you can roll it back...)
UPDATE EmployeeTable
SET EMailAddress = otherTable.EMail
WHERE EmployeeTable.FieldX = OtherTable.FieldY
Basically, you do the join in the WHERE clause.
May 6, 2019 at 7:33 pm
THERE HAVE BEEN MULTIPLE WARNINGS ON SSC AGAINST THIS CONSTRUCT!!!!!!!!!!!!!!!!!
UPDATE EmployeeTable
SET EMailAddress = otherTable.EMail
WHERE EmployeeTable.FieldX = OtherTable.FieldY
DO THIS INSTEAD!!!!!!!!!!!!!!!!!!!!!
UPDATE E
SET E.EmailAddress = S.Email
FROM EmployeeTable E
JOIN OtherTable S
ON E.FieldX = S.FieldY
(credit to some poster called Jeff Moden and apologies if I got it wrong)
May 6, 2019 at 9:29 pm
If I am understanding correctly, you want to match by position - the values in your username list with values in the email list.
Email List: (a1@email.com, a2@email.com, a3@email.com)
User List: (User1, User2, User3)
The end result would be equivalent to:
UPDATE table SET EmailAddress = 'a1@email.com' WHERE Username = 'User1'
UPDATE table SET EmailAddress = 'a2@email.com' WHERE Username = 'User2'
UPDATE table SET EmailAddress = 'a3@email.com' WHERE Username = 'User3'
If that is the case - then you would use a string split utility (search this site for DelimitedSplit8K_Lead). Something like this:
Declare @emailList varchar(1000) = 'a1@email.com,a2@email.com,a3@email.com'
, @userList varchar(1000) = 'User1,User2,User3';
With emailUsers
As (
Select Username = u.Item
, EmailAddress = em.Item
From dbo.DelimitedSplit8K(@userList, ',') u
Cross Apply dbo.DelimitedSplit8K(@emailList, ',') em
Where u.ItemNumber = em.ItemNumber
)
Update t
Set EmailAddress = eu.EmailAddress
From yourTable t
Inner Join emailUsers eu On eu.Username = t.UserName;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply