Updating a table using Common Table Expression

It is possible to update a table but only the rows returned by the common table expression are modified.

In the Employees table we consider two employees, 'Andrew Fuller' who lives in the city 'Tacoma' and Steven Buchanan who lives in 'London'. Note that the CTE definition has a filter for City='London'

This update query tries to change the LastName to 'Bardick' of an employee whose  FirstName='Andrew'
------------------
USE Northwind
Go
---define the common table expression
WITH EMp_CTE (FirstName,LastName,CITY)  /*Name: EMp_CTE, Column List:FirstName, --LastName,City*/
AS
---define the CTE query

(SELECT FirstName,LastName,City

from Employees

where City='London'
)
---Updating the table
Update EMp_CTE
Set LastName='Bardick'
Where FirstName='Andrew' 
Go
-----
--Andrew Fuller is from Seattle
--expect Andrew Fuller to become Andrew Bardick if the query were to succeed
--Since Andrew Fuller is not from London the table does not get updated
The above query returns '0' rows Table is not modified
-------------------
Now consider this next update query:

USE Northwind
Go
---define the common table expression
WITH EMp_CTE (FirstName,LastName,CITY)  /*Name: EMp_CTE, Column List:FirstName, --LastName,City*/
AS

---define the CTE query

(SELECT FirstName,LastName,City

from Employees

where City='London'
)
---Updating the table

Update EMp_CTE
Set LastName='Bardick'
Where FirstName='Steven' 
Go
--------------------------------------
--The response is 1 row gets updated.
--Steven Buchanan is from London                         
--expect Steven Buchanan to become Steven Buchanan if the query succeeds
--Since Steven Buchanan is from 'London' update will take place and it becomes Steven Bardick as --shown.

The syntax for the CTE can be found here:
Syntax here:
https://msdn.microsoft.com/en-us/library/ms177523.aspx