SQL Server Update with Join Statement

Suppose you’ve created the following join statement to retrieve all the store contacts who are Purchasing Managers from the Adventure Works database.

select PC.* from Person.Contact PC
join Sales.StoreContact SC on PC.ContactID = SC.ContactID
join Person.ContactType CT on SC.ContactTypeID = CT.ContactTypeID
where CT.Name = 'Purchasing Manager'
order by PC.LastName

Here is a snapshot of the results of the join. Notice that the AdditionalContactInfo field is null.

Now, suppose you would like to update the AdditionalContactInfo field just for the subset of contacts returned by your join.

Here is the SQL Update statement combined with the Join:

Update Person.Contact
set AdditionalContactInfo =
'<AdditionalContactInfo xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo" xmlns:crm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord" xmlns:act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">
<crm:ContactRecord>Call before 5:00 pm </crm:ContactRecord>
</AdditionalContactInfo>'
from Person.Contact PC
join Sales.StoreContact SC on PC.ContactID = SC.ContactID
join Person.ContactType CT on SC.ContactTypeID = CT.ContactTypeID
where CT.Name = 'Purchasing Manager'