UPDATE changes the values in existing rows, a raise for one employee, a corrected email address, a status flip. It's one of the simplest statements in SQL to write, and one of the easiest to run accidentally against the entire table if you're not careful.
Learning Objectives
- Write an UPDATE statement that changes one or more columns.
- Understand why WHERE is effectively mandatory on every UPDATE.
- Update rows using data pulled from a second table.
Target Rows
Matched by WHERE
SET Values
New column values applied
Rows Updated
Only the matched rows change
Basic UPDATE
SET lists the column (or columns, comma-separated) to change and their new values. WHERE narrows down which rows are affected, using the exact same conditions you already know from SELECT.
UPDATE employees SET salary = 99000 WHERE first_name = 'Grace' AND last_name = 'Hopper';
Updating Multiple Columns
Multiple columns in one SET, comma-separated. Notice salary = salary * 1.05, the new value can reference the column's own current value, a 5% raise applied to whatever each matched row's salary already was.
UPDATE employees SET department = 'Research', salary = salary * 1.05 WHERE department = 'Engineering' AND salary < 90000;
Why WHERE Is Effectively Mandatory
UPDATE employees SET salary = 99000 with no WHERE at all is perfectly valid SQL, and it sets every single employee's salary to 99000. There's no confirmation prompt, no undo button once it's committed. This is the single most consequential mistake a new SQL developer can make, and the fix is simple: write the WHERE clause, or a SELECT with the same WHERE to preview the affected rows, before running any UPDATE.
Many GUI tools (like MySQL Workbench) have a "safe update mode" that blocks UPDATE/DELETE without a WHERE on a key column by default. It's a useful safety net, but don't rely on it instead of actually checking your WHERE clause, scripts and command-line tools won't have it.
Updating Using a Second Table
Sometimes the new value isn't a literal, it depends on data in another table. The syntax for this genuinely differs by database.
UPDATE employees SET department = d.new_name FROM department_renames d WHERE employees.department = d.old_name;
UPDATE employees e JOIN department_renames d ON e.department = d.old_name SET e.department = d.new_name;
UPDATE employees SET department = ( SELECT d.new_name FROM department_renames d WHERE d.old_name = employees.department ) WHERE department IN (SELECT old_name FROM department_renames);
PostgreSQL and SQL Server both support UPDATE ... FROM. MySQL instead extends UPDATE with a JOIN clause directly. SQLite supports neither, so a correlated subquery does the same job.
Frequently Asked Questions
Does UPDATE return the updated rows?
Not by default, it returns a count of how many rows were affected. PostgreSQL supports an optional RETURNING clause, the same one covered for INSERT, to get the updated rows back in the same statement.
What happens if my WHERE clause matches zero rows?
Nothing fails, the UPDATE runs successfully and reports 0 rows affected. This is often a silent bug, your code assumed something was updated when nothing actually was.
Can I update a column to NULL?
Yes, SET column_name = NULL works exactly like any other value, as long as the column allows NULL (doesn't have a NOT NULL constraint).
Common Beginner Mistakes
Running UPDATE without a WHERE clause
This updates every row in the table, with no confirmation and no automatic undo once committed. Always write and double-check the WHERE clause before running an UPDATE, or run the same condition as a SELECT first to preview exactly which rows will be affected.
Assuming a 0-rows-affected result means an error occurred
It doesn't, it just means the WHERE clause matched nothing, which is valid and silent. Check the affected-row count in your application code if that distinction matters to your logic.
Forgetting that salary = salary * 1.05 applies per matched row, not once globally
Each row's new value is computed from that same row's own current value. This is usually what you want, but it's worth being explicit about if you're reasoning through what the update actually does.
Best Practices
- Run your WHERE condition as a SELECT first to preview exactly which rows will be affected, before running the UPDATE.
- Wrap any UPDATE you're not 100% sure about in a transaction (covered in a later lesson) so it can be rolled back if something looks wrong.
- Keep SET expressions simple and readable, split a complex multi-step change into separate UPDATE statements if it gets hard to reason about.
- Check the affected-row count your database driver returns, a 0 when you expected a change is worth investigating, not ignoring.
Interview Questions
Write an UPDATE that gives every Engineering employee a 10% raise.
UPDATE employees SET salary = salary * 1.10 WHERE department = 'Engineering'; — WHERE scopes it to the right rows, and salary * 1.10 computes each row's new value from its own current salary.
Why is UPDATE without WHERE considered one of the most dangerous mistakes in SQL?
It's syntactically valid, runs without any warning, and applies to every row in the table in one statement. Unlike a mistake caught by a type error or a constraint violation, there's nothing in the language itself that stops it, it's purely a matter of discipline and double-checking before running it.
How would you update employees' departments based on a mapping stored in another table, in PostgreSQL?
UPDATE employees SET department = d.new_name FROM department_renames d WHERE employees.department = d.old_name; — PostgreSQL's UPDATE ... FROM joins in the second table directly, letting SET reference its columns.
Summary
This lesson covered changing one or more columns with UPDATE ... SET, why a WHERE clause is effectively mandatory, and the different syntax each database uses to update rows based on a second table, UPDATE ... FROM, UPDATE ... JOIN, and a correlated subquery.
What's Next?
The next lesson covers DELETE, removing rows entirely, and how it compares to TRUNCATE.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.