Introduction
CTEs with data modifying statements
9. UPDATE statement
Summary

Instruction

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.

UPDATE GroceryStore
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.

Exercise

Our database contains the first names of customers. The first name of one of the customers, Ema, was misspelled. The correct spelling is Emma.

Change this wrong spelling for all customers with FirstName = N'Ema'.

Stuck? Here's a hint!

Use UPDATE ... SET ... WHERE ...