DOING A JOIN INSIDE AN UPDATE STATEMENT

This example is meant only to illustrate how to do an update statement that involves multiple tables.

dbo.CustomersMasterList Table

CML_ID, Name, Address, City, State, Zip, Phone, CustomerEmailAddress (newly added)

dbo.EmailAddresses Table

E_ID, emailaddress

Sample data from dbo.CustomersMasterList
1,John Doe,12345, Elm Street,Walawala WA, 90085,952-555-1212, NULL

2,Jane Doe,12345, Elm Street,Walawala WA, 90085,952-555-1212, NULL

...

Sample data from dbo.EmailAddresses

1,JohnDoe@aol.com

2,JaneDoe@aol.com

Say you need to take Table A Which is dbo.CustomersMasterList

and you needed to add a column to it called CustomerEmailAddress. So you go modify the table and add CustomerEmailAddress to the table.

But the email addresses are in a seperate table called dbo.EmailAddresses and its a big table so you dont want to retype all that data.

You could do this:

UPDATE dbo.CustomersMasterList

SET CustomerEmailAddress = EmailAddress

FROM dbo.CustomersMasterList CML

JOIN dbo.EmailAddresses ON

CML_ID = E_ID

WHERE any criteria can go here...

Let me know if you have any issues.... Happy programming!