SQL Server 2008 : SELECT two fields from db, append text, and UPDATE column with results

I have a web application that is syncing to LDAP/eDirectory and it's data is stored in a SQL Server 2008 Enterprise database.

Our organization has just migrated to MS/AD, and with this migration came a new user account naming convention, which of course changed everyone's email address.

The web application uses the EMAIL field in the CLIENT table of the database as a primary/required field, so, if I can modify the existing CLIENT.EMAIL recordset to reflect the new email address format, and sync to AD, all of the remaining account info will be updated without losing any accounts and without those accounts losing access to their history within the web application (I've tested this by mod开发者_开发技巧ifying a single EMAIL record and syncing to AD, then that account could login with AD credentials and see all account history that was created with the LDAP/eDir user account).

I could do this manually (updated 1,255 records), but I thought a SQL query would be a better solution.

What I have done so far is write the following SELECT statement (which has provided me with the result that I want to insert into the production database's EMAIL column):

SELECT FIRST_NAME + '.' + LAST_NAME + '@email.com' FROM db_name.TABLE_NAME

What I need to be able to do with this result is UPDATE the EMAIL field/column (which currently has a LAST_NAMEFIRST_INITIAL@email.com format) with the result of the above SELECT statement.

If I add an: UPDATE CLIENT.EMAIL line above this statement, I get a syntax error on the SELECT portion shown above.

I want to:

• take the existing EMAIL field (which shows smithj@email.com for client John Smith),
• then SELECT the first and last name fields separated by a '.' and append @email.com to the end so that I end up with john.smith@email.com for that same record (and UPDATE the entire EMAIL field/column with the appropriate entry based on a FIRST_NAME.LAST_NAME@email.com format.

Is this possible? If so, how do I get around the syntax errors I keep getting? Any assistance would be GREATLY appreciated.

---

UPDATE db_name.TABLE_NAME
SET EMAIL = FIRST_NAME + '.' + LAST_NAME + '@email.com'

---

Assuming you have two separate tables and without knowing the ID linking but this should help

UPDATE Client SET email =
    (SELECT FIRST_NAME + '.' + LAST_NAME + '@email.com' 
     FROM db_name.TABLE_NAME AS ldap WHERE ldap.ADGuid = Client.ADGuid)

If it's only one table then all you need is

UPDATE Client SET email = SELECT FIRST_NAME + '.' + LAST_NAME + '@email.com' 

---

First, check if this shows what you want:

SELECT FIRST_NAME
     , LAST_NAME
     , email AS currentEmail
     , FIRST_NAME + '.' + LAST_NAME
       + SUBSTRING(email, CHARINDEX('@', email), LEN(email)) 
       AS EmailToBeWritten
FROM Client ; 

Then, if you are absolutely sure that the EmailToBeWritten is showing exactly what you want to update the email field with, use:

UPDATE Client 
  SET email = FIRST_NAME + '.' + LAST_NAME
              + SUBSTRING(email, CHARINDEX('@', email), LEN(email)) ;

---

Join the directory table to the client table and do the update like so:

UPDATE c 
SET c.EMAIL = d.FIRST_NAME + '.' + d.LAST_NAME 
FROM CLIENT c 
INNER JOIN DIRECTORY d ON (whatever you need to do to join them goes here.  you can join using multiple criteria with substrings, for instance)