It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Using CTEs with data-modifying statements
9. UPDATE statement
Summary

Instruction

Very nice. Now you can insert new data into a table using a CTE. Next, we'll discuss 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 grocery_store
SET city = 'San Diego', name = 'Green Grocery Store'
WHERE id = 3;

This statement changes the city to 'San Diego' and the store name to 'Green Grocery Store' where the id = 3. Notice that the statement begins with the UPDATE keyword and the table name.

Next, we see the SET keyword, followed by the names of columns to be modified. The new values (i.e. the ones we'll be inserting) are specified after the equality sign ( = ). Notice that all the column names are separated by commas.

At the end, the WHERE clause limits the update to the record with id = 3. Otherwise the city and name columns of all records would be changed.

Exercise

The first name of one of the customers was misspelled. The current spelling is Ema; the correct spelling is Emma.

Use UPDATE to change this wrong spelling for all customers with first_name = 'Ema'.

Stuck? Here's a hint!

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