Updating fields of a table with values from another table in MSSQL

Being quite adept with queries in MS Access, I was aware that you could join tables and update one table's fields with the values from an other table. However, I didn't know how, or if it was possible to do this in MSSQL. So, I did some research and found a solution.

The general structure of this type of update query in MSSQL follows a specific pattern.

1. Start with the traditional structure of an update table in MSSQL.
2. Put in the SET statements.
3. Use the table being updated as the FROM table.
4. Join the other tables you wish to pull values from.
5. Make you WHERE statements to control which records are update and selected.

Here is a simple sample that updates fields of one table with values from two other tables:

UPDATE tblOne SET tblOne.clientName=tblTwo.firstName+' '+tblTwo.lastName,
tblOne.clientAddress=tblThree.streetAddress+' '+tblThree.city+', '+tblThree.prov+' '+tblThree.postal
FROM tblOne
INNER JOIN tblTwo ON tblOne.clientID=tblTwo.id
INNER JOIN tblThree ON tblTwo.id=tblThree.userID
WHERE tblOne.clientID=1

Easy Peasy, and it saved me a lot of programming code in cfml to transfer information from one table to an existing record of an other table.