Very nice. You can insert data into tables using the CTE. Now, we'll talk how to use CTEs to update data in a table.
Remember the syntax of the regular
UPDATE statement which modifies data in the table. Look at the example below.
SET city = 'San Diego', name = 'Green Grocery Store'
WHERE id = 3;
This statement changes the city of the grocery store to
'San Diego' and the name of the grocery store to
'Green Grocery Store' for the grocery store with an
id = 3.
The statement begins with the
UPDATE keyword and table name. Next, we see the
SET keyword followed by names of 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 only to the record with
id = 3. Otherwise the city and name columns of all records would be changed.