Data stored in a database rarely stays the same forever. Prices change, customers move, orders are shipped, and inventory goes up and down. When you need to modify rows that already exist in a table, SQL provides the UPDATE statement.

In this tutorial you will learn how to change the value of one or more columns in one or more rows using UPDATE, how to combine it safely with WHERE, how to use expressions and values from other tables, and — most importantly — how to avoid the single most common accident in SQL: updating every row in a table by mistake.

Prerequisites

To follow along, you need:

• A working SQL database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).
• A client where you can execute SQL statements, such as DBeaver, psql, mysql, or sqlite3.
• Basic familiarity with SELECT and the WHERE clause. If you have not read the previous tutorial on filtering rows with WHERE, do that first — UPDATE relies on the same filtering rules.

If you have not created a sample database yet, the setup script in the next section will produce everything you need.

Sample Database

We continue with the small online bookstore schema introduced in earlier tutorials. It has two tables: authors and books.

CREATE TABLE authors (
    author_id   INTEGER      PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    country     VARCHAR(50)
);

CREATE TABLE books (
    book_id      INTEGER       PRIMARY KEY,
    title        VARCHAR(200)  NOT NULL,
    author_id    INTEGER       NOT NULL,
    category     VARCHAR(50),
    price        DECIMAL(8, 2) NOT NULL,
    stock        INTEGER       NOT NULL,
    published_on DATE,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

INSERT INTO authors (author_id, author_name, country) VALUES
    (1, 'Jane Austen',              'United Kingdom'),
    (2, 'Haruki Murakami',          'Japan'),
    (3, 'Chimamanda Ngozi Adichie', 'Nigeria'),
    (4, 'Gabriel Garcia Marquez',   'Colombia');

INSERT INTO books (book_id, title, author_id, category, price, stock, published_on) VALUES
    (1, 'Pride and Prejudice',           1, 'Classic',      12.50, 20, '1813-01-28'),
    (2, 'Emma',                          1, 'Classic',      10.00,  0, '1815-12-23'),
    (3, 'Norwegian Wood',                2, 'Fiction',      15.75, 12, '1987-09-04'),
    (4, 'Kafka on the Shore',            2, 'Fiction',      18.20,  5, '2002-09-12'),
    (5, 'Half of a Yellow Sun',          3, 'Historical',   16.00,  8, '2006-08-11'),
    (6, 'Americanah',                    3, 'Contemporary', 14.50,  3, '2013-05-14'),
    (7, 'One Hundred Years of Solitude', 4, 'Classic',      22.00, 25, '1967-05-30'),
    (8, 'Love in the Time of Cholera',   4, 'Classic',      19.99,  0, '1985-09-05'),
    (9, 'Unknown Title',                 2, NULL,           13.00,  4, NULL);

Basic Syntax

An UPDATE statement changes the values of specified columns in the rows that match a condition.

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

Each clause has a specific role:

• UPDATE table_name — the table whose rows will be modified.
• SET column = value — the columns to change, and the new values or expressions. You can update several columns in a single statement by separating them with commas.
• WHERE condition — restricts which rows are updated. If you omit WHERE, every row in the table is updated.

Warning: An UPDATE without a WHERE clause modifies every row in the table. Practice these statements only in a disposable learning database or after taking a verified backup.

Verify Before You Modify

Before running an UPDATE, always run a SELECT with the same WHERE clause first. This lets you see exactly which rows will be affected.

SELECT
    book_id,
    title,
    price
FROM books
WHERE book_id = 2;

Only after you have confirmed the target rows should you run the actual UPDATE.

Practical Example

Suppose the bookstore wants to correct the price of Emma (book id 2) to 11.00.

Step 1 — inspect the row:

SELECT
    book_id,
    title,
    price
FROM books
WHERE book_id = 2;

book_id	title	price
2	Emma	10.00

Step 2 — update the row:

UPDATE books
SET price = 11.00
WHERE book_id = 2;

Step 3 — verify the change:

SELECT
    book_id,
    title,
    price
FROM books
WHERE book_id = 2;

Expected Result

book_id	title	price
2	Emma	11.00

Most database clients also report the number of rows affected (for example, UPDATE 1), which is a useful sanity check.

Additional Examples

1. Updating Multiple Columns at Once

You can change several columns in one statement by listing them in the SET clause.

UPDATE books
SET price    = 20.00,
    category = 'Literary Classic'
WHERE book_id = 7;

This is more efficient than running two separate updates and guarantees that both changes happen together.

2. Using Expressions in SET

The new value does not have to be a constant. It can be any expression, including one that references the current value of the column.

Apply a 10% discount to every book in the 'Classic' category:

UPDATE books
SET price = price * 0.90
WHERE category = 'Classic';

Increase the stock of Kafka on the Shore by 3 copies:

UPDATE books
SET stock = stock + 3
WHERE book_id = 4;

The right-hand side of = sees the value before the update, so stock = stock + 3 means “new stock equals old stock plus three”.

3. Updating Many Rows That Share a Condition

UPDATE is not limited to a single row. Any WHERE condition that is valid in a SELECT is also valid here.

Mark every out-of-stock book as 'Fiction' no — better example — bump the price of every book published before the year 2000:

UPDATE books
SET price = price + 1.00
WHERE published_on < DATE '2000-01-01';

Before running it, check which rows will change:

SELECT
    book_id,
    title,
    published_on,
    price
FROM books
WHERE published_on < DATE '2000-01-01';

4. Setting a Column to NULL

To clear a value, assign NULL in the SET clause. Note the difference between the assignment = NULL (valid) and the comparison = NULL (invalid — you must use IS NULL for comparisons).

UPDATE books
SET category = NULL
WHERE book_id = 9;

The column must allow NULL (that is, it must not have a NOT NULL constraint) or the statement will fail.

5. Using a Value from Another Table

Sometimes the new value depends on data in a different table. A portable way to express this is a subquery in SET.

Suppose the authors table gains a discount_rate column, and you want every book to inherit its author’s discount. In ANSI-standard SQL you can write:

UPDATE books
SET price = price * (
    SELECT 1 - a.discount_rate
    FROM authors AS a
    WHERE a.author_id = books.author_id
)
WHERE EXISTS (
    SELECT 1
    FROM authors AS a
    WHERE a.author_id = books.author_id
);

PostgreSQL and SQL Server also support the more readable UPDATE ... FROM extension, and MySQL/MariaDB support a joined UPDATE. These are labeled in the Database Compatibility section below.

6. Limiting the Number of Rows Updated

Some databases allow you to cap the number of updated rows, which is useful for controlled batch updates.

MySQL, MariaDB, SQLite

UPDATE books
SET stock = stock + 1
WHERE category = 'Classic'
ORDER BY book_id
LIMIT 3;

PostgreSQL, SQL Server, and Oracle Database do not support LIMIT directly in UPDATE. On those systems you typically restrict the target rows through a subquery on the primary key.

7. Updating from Application Code

When the new value comes from user input, always use a parameterized query. This prevents SQL injection and lets the database reuse the prepared plan.

String sql = """
        UPDATE books
        SET price = ?
        WHERE book_id = ?
        """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setBigDecimal(1, newPrice);
    statement.setLong(2, bookId);

    int updatedRows = statement.executeUpdate();
    if (updatedRows == 0) {
        // No row matched the WHERE clause — decide how to handle it.
    }
}

executeUpdate() returns the number of affected rows, which you should check when you expect exactly one row to change.

8. Wrapping Related Updates in a Transaction

When two or more updates must succeed together — or fail together — run them inside a transaction.

BEGIN;

UPDATE books
SET stock = stock - 1
WHERE book_id = 3;

UPDATE books
SET stock = stock + 1
WHERE book_id = 4;

COMMIT;

If something goes wrong between the two statements, ROLLBACK reverts both changes so the total stock across the two books stays consistent. Transaction behavior varies between database systems and storage engines; consult your database’s documentation for details.

Common Mistakes

Forgetting the WHERE Clause

Incorrect:

UPDATE books
SET price = 9.99;

This is syntactically valid, but it sets the price of every book in the table to 9.99. Always double-check that a WHERE clause is present before executing an UPDATE.

Comparing to NULL with =

Incorrect:

UPDATE books
SET category = 'Uncategorized'
WHERE category = NULL;

The expression category = NULL evaluates to unknown, never true, so this statement updates zero rows.

Correct:

UPDATE books
SET category = 'Uncategorized'
WHERE category IS NULL;

Assuming an UPDATE Always Affects a Row

If the WHERE condition matches nothing, UPDATE succeeds and reports zero rows affected. It does not raise an error. In application code, check the returned row count when you expect exactly one row to change.

Mixing AND and OR Without Parentheses

Incorrect:

UPDATE books
SET price = price * 0.90
WHERE category = 'Classic' OR category = 'Fiction'
  AND stock > 0;

Because AND binds tighter than OR, this discounts all classic books plus in-stock fiction books, which is probably not what you want.

Correct:

UPDATE books
SET price = price * 0.90
WHERE (category = 'Classic' OR category = 'Fiction')
  AND stock > 0;

Reading a Column Twice in One SET

Incorrect assumption:

UPDATE books
SET price = price * 1.10,
    stock = price;         -- Which "price" is this — old or new?

In standard SQL, all right-hand-side expressions in a single UPDATE see the row’s values as they were before the statement started. So stock = price here uses the old price, not the newly increased one. Behavior in edge cases can differ between databases, so avoid relying on evaluation order — use two statements (inside a transaction) or a temporary variable if you need the intermediate value.

Database Compatibility

The basic form of UPDATE shown in the Basic Syntax section is part of ANSI SQL and works in:

• PostgreSQL
• MySQL
• MariaDB
• SQL Server
• Oracle Database
• SQLite

Notable differences you may encounter:

• UPDATE ... FROM. PostgreSQL and SQL Server support an extension that joins other tables directly:

PostgreSQL, SQL Server

UPDATE books
  SET price = price * (1 - a.discount_rate)
  FROM authors AS a
  WHERE a.author_id = books.author_id;

• Multi-table UPDATE. MySQL and MariaDB use a JOIN directly in the UPDATE:

MySQL, MariaDB

UPDATE books AS b
  JOIN authors AS a
      ON a.author_id = b.author_id
  SET b.price = b.price * (1 - a.discount_rate);

• UPDATE ... LIMIT. Supported by MySQL, MariaDB, and SQLite. PostgreSQL, SQL Server, and Oracle Database do not support it directly; use a subquery to restrict the target rows.
• RETURNING. PostgreSQL, Oracle Database, and modern SQLite/MariaDB support UPDATE ... RETURNING, which returns the updated rows in the same statement. SQL Server offers the OUTPUT clause with similar semantics.
• Boolean and date literals. The DATE '2000-01-01' form is portable to most modern databases, but Oracle Database users sometimes prefer TO_DATE(...).

When in doubt, consult the documentation for the database and version you are using.

Best Practices

• Always test the WHERE clause with SELECT first. Confirm the exact set of rows before modifying them.
• Never rely on the absence of a WHERE clause. If you truly want to change every row, say so explicitly with a comment and a code review.
• Update only the columns that must change. Leaving other columns out of the SET list prevents accidental overwrites.
• Use parameters, not string concatenation. This prevents SQL injection and improves plan reuse in application code.
• Wrap related updates in a transaction. If two changes must be consistent with each other, they belong in one transaction with an explicit COMMIT or ROLLBACK.
• Check the affected-row count. In application code, verify that the number of updated rows matches your expectation.
• Handle NULL explicitly. Use IS NULL and IS NOT NULL in the WHERE clause when a column can be missing.
• Prefer readable expressions. price = price * 0.90 communicates a 10% discount more clearly than a precomputed constant.

Conclusion

You learned how to use the SQL UPDATE statement to modify existing rows, how to change multiple columns at once, how to use expressions that reference the current value, and how database-specific extensions can make cross-table updates more readable. Above all, remember that an UPDATE without a WHERE clause changes every row in the table — always verify the target rows with a SELECT first, and wrap related changes in a transaction when consistency matters.