SQL Lesson 14: Updating Rows

Learn to modify existing data in databases using UPDATE statements with proper WHERE conditions.

SQL Lesson 14: Updating Rows

In addition to adding new data, a common task is to update existing data, which can be done using anUPDATE 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 WHEREclause (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.

⚠️ Critical UPDATE Safety Tips:

Always use WHERE: Without it, ALL rows will be updated
Test with SELECT first: Verify which rows will be affected
Use specific conditions: Target exact rows with unique identifiers
Backup critical data: Have recovery plans for production updates
Double-check syntax: Ensure proper column names and data types

UPDATE Best Practices:

1. Test your WHERE clause first:

SELECT * FROM movies WHERE id = 2; -- Test first
UPDATE movies SET director = "New Director" WHERE id = 2; -- Then update

2. Update multiple columns at once:

UPDATE movies SET title = "New Title", year = 2023 WHERE id = 1;

3. Use expressions in SET clause:

UPDATE movies SET year = year + 1 WHERE year < 2000;

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.

Table: movies

Exercise 14 — Tasks

The director for A Bug's Life is incorrect, it was actually directed by John Lasseter
The year that Toy Story 2 was released is incorrect, it was actually released in 1999
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

Solve all tasks to continue to the next lesson.

Progress0 of 3 tasks completed

Data Corruption Detected!

The movies table contains incorrect information highlighted in red. Use UPDATE statements to fix these data quality issues. Remember to always use WHERE clauses to target specific rows!

Corrupted Data:

A Bug's Life director is "El Directore" (should be "John Lasseter")
Toy Story 2 year is 1899 (should be 1999)
Movie #11 title is "Toy Story 8" and director is "El Directore" (should be "Toy Story 3" by "Lee Unkrich")