LearnContact
Lesson 2816 min read

UPDATE Statement

Learn how to modify existing rows in a table using the UPDATE statement, and why the WHERE clause is critical.

Introduction

Data rarely stays correct forever. A student's grade needs correcting, a customer changes their address, a product's price changes. The UPDATE statement is how you modify data that already exists in a table, without deleting and re-inserting it.

UPDATE is one of the most powerful — and most dangerous — statements in SQL. Used carelessly, it can silently overwrite every row in a table in a fraction of a second.

What You Will Learn
  • The basic UPDATE ... SET ... WHERE syntax.
  • How to update multiple columns in a single statement.
  • Why the WHERE clause is absolutely critical.
  • A worked example updating one student's grade.

UPDATE Syntax

The basic form of an UPDATE statement names the table, sets one or more columns to new values, and then filters which rows should be affected with WHERE.

UPDATE table_name
SET column = value
WHERE condition;

For example, suppose a "students" table needs Sam's marks corrected to 70.

UPDATE students
SET marks = 70
WHERE name = 'Sam';
idnamemarks
1Alex78
2Sam70
3Priya91

Only the row where name equals 'Sam' was changed. Every other row in the table was left untouched, because the WHERE clause matched exactly one row.

Updating Multiple Columns

You are not limited to updating a single column. Separate multiple column assignments with commas inside the same SET clause, and MySQL updates them all in one statement.

UPDATE students
SET marks = 85, grade = 'A'
WHERE name = 'Sam';
Result
+----+-------+-------+-------+
| id | name  | marks | grade |
+----+-------+-------+-------+
| 1  | Alex  | 78    | B     |
| 2  | Sam   | 85    | A     |
| 3  | Priya | 91    | A     |
+----+-------+-------+-------+
Tip

You can update a column based on its own current value too, such as SET marks = marks + 5, which adds 5 to whatever the existing value already is.

The Critical Importance of WHERE

The WHERE clause is what limits an UPDATE to specific rows. If you omit it, MySQL will not ask for confirmation — it will simply apply the SET clause to every single row in the table.

-- DANGER: no WHERE clause
UPDATE students
SET marks = 0;
Warning: This Updates Every Row

Running an UPDATE statement without a WHERE clause changes every row in the entire table. In the example above, every single student's marks would be reset to 0 — including Alex and Priya, who were never meant to be touched. This is one of the most common and most damaging mistakes beginners make with SQL. Always double-check your WHERE clause before pressing execute.

This is not unique to student data — the same mistake on an "orders" or "employees" table in a real production database can be catastrophic and, in many cases, impossible to fully undo.

Worked Example

Suppose Alex retook an exam and improved from a 78 to an 88, and their grade should be updated to reflect the new score.

update_example.sql
-- Before: Alex has marks = 78, grade = 'B'
UPDATE students
SET marks = 88, grade = 'A'
WHERE name = 'Alex';

SELECT * FROM students WHERE name = 'Alex';
Result
+----+------+-------+-------+
| id | name | marks | grade |
+----+------+-------+-------+
| 1  | Alex | 88    | A     |
+----+------+-------+-------+

Both columns changed together in a single statement, and the WHERE clause ensured only Alex's row was affected.

Common Mistakes

Avoid These Mistakes
  • Forgetting the WHERE clause entirely, updating every row in the table by accident.
  • Using a WHERE condition that matches more rows than intended, such as a typo in a name.
  • Forgetting the comma between multiple SET assignments.
  • Confusing = for comparison in WHERE with = for assignment in SET — both use the same symbol but mean different things in each part of the statement.

Best Practices

  • Always write and check the WHERE clause before running an UPDATE.
  • Run the equivalent SELECT with the same WHERE clause first, to preview exactly which rows will be affected.
  • Filter on a unique column like an id whenever possible, rather than a name that could repeat.
  • When working with important data, run updates inside a transaction so mistakes can be rolled back.

Frequently Asked Questions

What happens if I forget the WHERE clause?

Every row in the table gets updated with the same SET values. This is almost never what you want and can be very destructive.

Can I update more than one column at a time?

Yes. Separate each column = value pair with a comma inside the SET clause, and all of them are applied together.

Can I undo an UPDATE?

Only if it was run inside a transaction that has not yet been committed, or if you have a backup. Once committed, there is no built-in "undo" for an UPDATE.

Can WHERE match more than one row in an UPDATE?

Yes. If the condition matches multiple rows, every matching row is updated, not just the first one found.

Key Takeaways

  • UPDATE table SET column = value WHERE condition; modifies existing rows.
  • Multiple columns can be updated in a single statement, separated by commas.
  • Omitting WHERE updates every row in the table — always double-check it before running.
  • Filtering on a unique column like id is the safest way to target exactly one row.

Summary

The UPDATE statement modifies existing rows using UPDATE ... SET ... WHERE. It can update one or many columns at once, but the WHERE clause is what keeps the change scoped to the rows you actually intend to touch.

In this lesson, you learned the UPDATE syntax, how to update multiple columns together, and why WHERE is non-negotiable. Next, you will learn the DELETE statement, which removes rows entirely.

Lesson 28 Completed
  • You can write an UPDATE statement with SET and WHERE.
  • You can update multiple columns in one statement.
  • You understand why omitting WHERE is dangerous.
  • You are ready to learn how to delete rows.
Next Lesson →

DELETE Statement