In addition to adding new data, a common task is to update existing data, which can be done using an UPDATE statement. Similar to the INSERT statement, you have to specify exactly which table, columns, and rows to update. In addition, the data you are updating has to match the data type of the columns in the table schema.

Update statement with values

UPDATE mytable SET column = value_or_expr, other_column = another_value_or_expr, … WHERE condition;

The statement works by taking multiple column/value pairs, and applying those changes to each and every row that satisfies the constraint in the WHERE clause.

Taking care

Most people working with SQL will make mistakes updating data at one point or another. Whether it's updating the wrong set of rows in a production database, or accidentally leaving out the WHERE clause (which causes the update to apply to all rows), you need to be extra careful when constructing UPDATE statements.

One helpful tip is to always write the constraint first and test it in a SELECT query to make sure you are updating the right rows, and only then writing the column/value pairs to update.

Exercise

It looks like some of the information in our Movies database might be incorrect, so go ahead and fix them through the exercises below.

1. The director for A Bugs Life is incorrect, it was actually directed by **John Lasseter**
	1. UPDATE movies SET Director = 'John Lasseter' WHERE Id = 2;
2. The year that Toy Story 2 was released is incorrect, it was actually released in **1999**
	1. UPDATE movies SET Year = 1999 WHERE Id = 3;
3. Both the title and director for Toy Story 8 is incorrect! The title should be "Toy Story 3" and it was directed by **Lee Unkrich**
	1. UPDATE movies SET Title = 'Toy Story 3', Director = 'Lee Unkrich' WHERE Id = 11;

Exercise 14 — Tasks

1. The director for A Bugs Life is incorrect, it was actually directed by **John Lasseter** ✓
2. The year that Toy Story 2 was released is incorrect, it was actually released in **1999** ✓
3. Both the title and director for Toy Story 8 is incorrect! The title should be "Toy Story 3" and it was directed by **Lee Unkrich** ✓

This webpage is "SQL Lesson 14: Updating rows" from SQLBolt. It teaches how to use the SQL UPDATE statement to modify existing records in a table. The main points include:

• The UPDATE statement allows you to specify which table, columns, and rows to update, with the format:

  UPDATE table_name
  SET column1 = value1, column2 = value2, ...
  WHERE condition;
  

• Emphasizes caution: mistakes such as omitting the WHERE clause can update all rows unintentionally.
• Suggestion: always test your WHERE clause with a SELECT query before using it in an UPDATE.

Exercise Section:

• Provides a sample "Movies" table.
• Asks the user to correct errors:
  • Change the director for "A Bug's Life" to John Lasseter.
  • Update the year of "Toy Story 2" to 1999.
  • Fix "Toy Story 8": change its title to "Toy Story 3" and director to Lee Unkrich.

The lesson is interactive, with query exercises and a solution link, helping users practice updating rows in SQL.

1