Skip to content
Tutorials Center
My learning

Topics

Lesson 58 of 125 ยท Changing Data

UPDATE

Learn SQL UPDATE with live examples. Change one or many columns, compute new values, update from other tables with UPDATE ... FROM, and avoid the missing-WHERE disaster.

Data rarely stays the same after it's inserted. Prices change, orders ship, customers move to a new city. The UPDATE statement changes values in rows that already exist: you say which table, which columns get which new values, and which rows to touch.

That last part is where the danger lies. An UPDATE without a WHERE clause changes every row in the table, and the database won't ask whether you're sure. In this lesson you'll learn the syntax, how to compute new values from old ones, how to update rows using data from other tables, and the habits professionals use so a typo never wipes out a column. The examples use the shop database, reset on every run.

The basic form

SQL
UPDATE table_name
SET column_1 = new_value,
    column_2 = new_value
WHERE condition;

SET lists the changes, and WHERE picks the rows, exactly like it does in a SELECT. The cast iron skillet (product 1) costs 34.90; put it on sale for 29.90:

SQL Update one row ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The runner reports OK, 1 row affected. The count is your first safety check: if you expected one row and see 26, something went wrong. Filtering by the primary key, as here, guarantees that at most one row matches.

Computing new values from old ones

The expression on the right of = can use the row's current values. That's how you raise prices by a percentage or adjust stock without first reading the old number. The query below raises every Audio product (category 4) by 8%, rounds to cents, and uses RETURNING to show the updated rows:

SQL Expressions in SET ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

RETURNING works with UPDATE in SQLite 3.35+, PostgreSQL and MariaDB; SQL Server uses OUTPUT inserted.price (and deleted.price for the old value). It's a handy way to see exactly what changed.

All SET expressions see the old row

In standard SQL, every expression in SET is computed from the row as it was before the update. Assignments don't see each other's results. A neat consequence is that you can swap two columns in one statement:

SQL Swapping two columns

Press Run (or Ctrl + Enter) to see the result here.

Both columns end up swapped, because last_name = first_name uses the old first name. SQLite, PostgreSQL, SQL Server and Oracle all behave this way.

MySQL is the exception. In a single-table UPDATE, MySQL applies assignments left to right, so later expressions see earlier results. The swap above would set both columns to the old last name. In MySQL, swap through a temporary value or a self-join.

The missing WHERE: the classic disaster

Here is the mistake every database professional fears. Someone means to mark one product as sold out but forgets the WHERE clause:

SQL Forgetting WHERE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

OK, 26 rows affected. Every product is now sold out. In a real shop, the storefront would immediately show nothing for sale, and unless you have a backup or the statement ran inside a transaction you can still roll back, the old stock numbers are gone.

A few habits make this almost impossible:

  1. Write the WHERE first. Start with SELECT * FROM products WHERE id = 7;, check the rows it returns, then turn it into an UPDATE with the same condition.
  2. Check the affected-row count. It should match the number of rows your SELECT returned.
  3. Use a transaction for anything risky. Run BEGIN;, then the update, look at the result, and only then COMMIT; (or ROLLBACK; to undo). See Transactions.
  4. Turn on guard rails. MySQL's sql_safe_updates setting (the --safe-updates client option) rejects UPDATE and DELETE statements that have no key-based WHERE or LIMIT. Many GUI tools have a similar warning.

Conditional updates with CASE

Sometimes different rows need different new values. You could run one UPDATE per rule, but a CASE expression handles them all in one pass. This restock rule gives sold-out products 25 units and tops up low-stock products by 10:

SQL UPDATE with CASE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The WHERE limits the update to products with fewer than 20 units, and the CASE decides the new value for each of them. Keep a WHERE even when the CASE has an ELSE stock branch: without it the database would still rewrite every row, which costs time, fires triggers and bumps the row count.

Setting and testing NULL

To clear a value, assign NULL with =: SET due_on = NULL. That's an assignment, so it works. In the WHERE clause, however, you must test for missing values with IS NULL, because = NULL is never true (see Working with NULL):

SQL
UPDATE employees
SET email = '[email protected]'
WHERE email IS NULL;   -- not: WHERE email = NULL

Updating with data from other tables

Often the new value lives in another table. There are two portable approaches and one popular extension.

Subqueries in WHERE and SET

A subquery in WHERE picks rows based on another table. A correlated subquery in SET computes a value for each row. Both are standard SQL and run everywhere.

The next example "reserves" stock for pending orders: for each product that appears in a pending order, it subtracts the total quantity those orders contain.

SQL Subqueries in an UPDATE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The WHERE ... IN matters: without it, products with no pending orders would get stock - NULL, which is NULL, and the NOT NULL rule on stock would stop the statement. Look at the results: a few products now show negative stock. The pending orders want more units than the warehouse has, which is exactly the kind of thing this report should reveal.

UPDATE ... FROM

Repeating the subquery twice is clumsy. PostgreSQL, SQL Server and SQLite (3.33+) let you join other tables into an update with a FROM clause. Here a price_changes table holds new prices from a supplier, and one statement applies them:

SQL UPDATE ... FROM ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The target table (products) is named after UPDATE, and the FROM lists the tables joined to it. Each product that matches a row in price_changes gets that row's price; products without a match are left alone. Make sure each target row matches at most one source row. If it matched several, the database would pick one of them in an unpredictable way.

The syntax differs between databases:

Database Updating with a join
SQLite 3.33+, PostgreSQL UPDATE products SET price = pc.new_price FROM price_changes AS pc WHERE pc.product_id = products.id;
SQL Server UPDATE p SET price = pc.new_price FROM products AS p JOIN price_changes AS pc ON pc.product_id = p.id;
MySQL / MariaDB UPDATE products AS p JOIN price_changes AS pc ON pc.product_id = p.id SET p.price = pc.new_price;
Any database correlated subquery in SET plus WHERE EXISTS or IN

For "insert the row if it's new, otherwise update it", don't combine an UPDATE and an INSERT by hand; use an upsert.

Constraints still apply

An update must leave every row valid. If one row would break a CHECK, NOT NULL, UNIQUE or foreign key rule, the whole statement fails and no row is changed. This discount takes 10 off every Stationery product, but the dot grid notebook only costs 9.90. The example fails on purpose:

SQL A rule-breaking update ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The run stops with Error: CHECK constraint failed: price >= 0. The other three Stationery products keep their old prices too, because a single statement is all-or-nothing. Try SET price = MAX(price - 10, 0) instead; SQLite's two-argument MAX() returns the larger value (other databases call it GREATEST()).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does UPDATE products SET stock = 0; do?

    Without WHERE, an UPDATE changes every row. Only special settings such as MySQL's sql_safe_updates reject it.
  2. 2. A row has a = 1 and b = 2. After UPDATE t SET a = b, b = a; in SQLite or PostgreSQL, what are a and b?

    All SET expressions are computed from the old row, so the values swap. MySQL is the exception, giving a = 2, b = 2.
  3. 3. Which condition correctly targets rows whose email is missing?

    Comparisons with = NULL are never true. Use IS NULL in WHERE; SET email = NULL is fine because it is an assignment.
  4. 4. An UPDATE matches 4 rows and the new value for one of them breaks a CHECK constraint. What happens?

    A statement is atomic. One failing row makes the whole UPDATE fail, and its earlier changes are undone.
  5. 5. What is the safest first step before running an UPDATE on production data?

    Previewing the rows with an identical WHERE shows exactly what the UPDATE will touch, and gives you a row count to compare with.

Summary

  • UPDATE table SET column = value WHERE condition; changes existing rows. The WHERE picks the rows.
  • Without WHERE, every row changes. Preview with a SELECT, check the row count, and use transactions for risky changes.
  • SET expressions can use the row's current values (price * 1.08), and all of them see the old row (except in MySQL).
  • CASE applies different rules to different rows in one statement.
  • Use subqueries for portable updates from other tables, or UPDATE ... FROM (SQLite, PostgreSQL, SQL Server) and UPDATE ... JOIN (MySQL).
  • RETURNING shows the changed rows. A rule-breaking row makes the whole statement fail.

Next: DELETE, where you'll remove rows safely.