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