Very nice. You can now insert data into tables using a CTE. Next, we'll talk about how to use CTEs to update data in a table.
First, let's review the syntax of the regular
UPDATE statement, which modifies data in a table. Look at the example below.
SET City = N'San Diego', Name = N'Green Grocery Store'
WHERE Id = 3;
This statement changes the city of the grocery store to
N'San Diego' and the name of the grocery store to
N'Green Grocery Store' for the grocery store where the
Id = 3.
The statement begins with the
UPDATE keyword and the table name. Next, we see the
SET keyword, followed by the names of the columns to be modified, with new values specified after the equality sign (
=). Notice that all these columns are separated by commas. At the end, the
WHERE clause limits the update to the record where the
Id = 3. Otherwise, the city and name columns of all records would be changed.