10.5 UPDATE

Table of Contents

Understanding `UPDATE` in SQL

The UPDATE statement changes existing data in a table. You do not create or remove rows with UPDATE, you only modify the values in rows that are already there.

This chapter focuses on how to use UPDATE effectively and safely.

Key rule:
Always use UPDATE with a proper WHERE clause unless you really mean to change every single row in the table.

Basic `UPDATE` Syntax

The general form of an UPDATE statement is:

sql

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

Important parts:

Part	Meaning
`UPDATE`	Says which table you want to modify
`SET`	Lists the columns and their new values
`WHERE`	Chooses which rows to update

If you omit WHERE, all rows in the table are updated.

Simple `UPDATE` Examples

Assume we have a users table:

sql

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    username    VARCHAR(50),
    email       VARCHAR(100),
    is_active   BOOLEAN,
    country     VARCHAR(50),
    sign_up_date DATE
);

Update a single row by primary key

sql

UPDATE users
SET email = 'new-email@example.com'
WHERE id = 5;

This changes the email for the user whose id is 5. No other rows are touched.

Update multiple columns in one row

sql

UPDATE users
SET email = 'alice.new@example.com',
    country = 'Canada'
WHERE username = 'alice';

You can update several columns at once. Separate each column = value pair with a comma.

Update multiple rows at once

sql

UPDATE users
SET country = 'Unknown'
WHERE country IS NULL;

Every user whose country is NULL will now have country = 'Unknown'.

Updating All Rows

Sometimes you really want to update every row.

sql

UPDATE users
SET is_active = TRUE;

This sets is_active = TRUE for all users.

Dangerous operation:
UPDATE table_name SET ...; with no WHERE clause changes every row.
Always double check before running such a statement.

A safer habit: first run a SELECT with the same WHERE to see which rows will be affected.

sql

SELECT * FROM users WHERE country = 'France';
UPDATE users
SET is_active = FALSE
WHERE country = 'France';

Using Expressions in `UPDATE`

You are not limited to fixed values. You can use expressions and functions.

Using arithmetic expressions

Assume a products table:

sql

CREATE TABLE products (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR(100),
    price     NUMERIC(10, 2),
    stock     INT
);

Increase all prices by 10 percent:

sql

UPDATE products
SET price = price * 1.10;

Decrease stock by 1 for a specific product:

sql

UPDATE products
SET stock = stock - 1
WHERE id = 42;

Using string functions

sql

UPDATE users
SET email = LOWER(email);

Converts all emails to lowercase.

sql

UPDATE users
SET username = TRIM(username);

Removes spaces at the start and end of each username.

Using current date or time

Assume a last_login column:

sql

ALTER TABLE users
ADD COLUMN last_login TIMESTAMP;

Update when a user logs in:

sql

UPDATE users
SET last_login = NOW()
WHERE id = 10;

Updating Based on Current Column Values

You can reference the same row’s current values in the expression.

Example: Increase price only if it is below a limit

sql

UPDATE products
SET price = price + 5
WHERE price < 20;

Only products with price less than 20 become more expensive by 5.

Example: Mark low stock

Add a low_stock flag:

sql

ALTER TABLE products
ADD COLUMN low_stock BOOLEAN DEFAULT FALSE;

Now set low_stock where needed:

sql

UPDATE products
SET low_stock = TRUE
WHERE stock < 5;

Updating with `NULL` and Conditions

You can set values to NULL, and you can also use NULL in conditions.

Set a column to `NULL`

sql

UPDATE users
SET country = NULL
WHERE country = 'Unknown';

Use `IS NULL` and `IS NOT NULL`

sql

UPDATE users
SET is_active = FALSE
WHERE last_login IS NULL;

This might mean: users who never logged in are not active.

Updating Multiple Tables with Conditions

Most SQL systems do not allow you to update more than one table with a single UPDATE statement. You usually update one table at a time.

However, you can use conditions that involve other tables, often with a subquery or JOIN. The exact syntax depends on the database, but the idea is similar.

Example with a subquery

Assume we have:

sql

CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    user_id    INT,
    amount     NUMERIC(10, 2),
    status     VARCHAR(20)
);

Set is_active = TRUE for users who have at least one completed order:

sql

UPDATE users
SET is_active = TRUE
WHERE id IN (
    SELECT DISTINCT user_id
    FROM orders
    WHERE status = 'completed'
);

Here the subquery finds the user_ids that should be updated.

Updating with `JOIN` (Vendor Specific)

Different SQL databases have different syntax. You will see patterns like:

Example pattern (many systems)

sql

UPDATE users
SET is_active = TRUE
FROM orders
WHERE users.id = orders.user_id
  AND orders.status = 'completed';

Meaning: join users with orders, and update those users that have a completed order.

You should check the specific syntax for the database you use, but the concept is the same. You choose rows to update based on related tables.

Safe Update Strategies

UPDATE can be dangerous if used carelessly. Here are habits that help prevent accidents.

1. Always test with `SELECT` first

Before running:

sql

UPDATE users
SET is_active = FALSE
WHERE country = 'Germany';

Run this:

sql

SELECT * FROM users
WHERE country = 'Germany';

If too many rows show up, you can adjust your WHERE clause before changing data.

2. Use transactions for large or risky updates

If your database supports transactions, you can do:

sql

BEGIN;
UPDATE products
SET price = price * 0.5
WHERE name LIKE '%Old%';
-- Check results
SELECT * FROM products WHERE name LIKE '%Old%';
-- If you are happy:
COMMIT;
-- If you are not happy:
ROLLBACK;

Important:
Inside a transaction, COMMIT makes changes permanent.
ROLLBACK undoes all changes made in that transaction.

This gives you a way to undo a bad update.

3. Limit the number of rows (when supported)

Some databases support:

sql

UPDATE users
SET is_active = FALSE
WHERE country = 'France'
LIMIT 100;

This only updates 100 matching rows. You can repeat in small batches.

`UPDATE` with `RETURNING` (Where Available)

Many databases, such as PostgreSQL, let you return the affected rows.

sql

UPDATE users
SET is_active = TRUE
WHERE id = 7
RETURNING id, username, is_active;

This both updates the row and shows you the new state.

This can be helpful in applications:

Update something
Immediately get the new value to send back in an API response

If your database does not support RETURNING, you usually need a separate SELECT after the UPDATE.

Common `UPDATE` Use Cases

Here are some typical patterns you will see often.

1. Changing a user’s profile information

sql

UPDATE users
SET email = 'bob.new@example.com',
    country = 'Italy'
WHERE id = 123;

2. Soft deleting instead of hard deleting

Add a deleted_at column:

sql

ALTER TABLE users
ADD COLUMN deleted_at TIMESTAMP;

Use UPDATE instead of DELETE:

sql

UPDATE users
SET deleted_at = NOW()
WHERE id = 50;

Now the user is “deleted” logically, but the row still exists and can be restored.

3. Mark overdue orders

Assume due_date and is_overdue in orders:

sql

UPDATE orders
SET is_overdue = TRUE
WHERE due_date < CURRENT_DATE
  AND status = 'pending';

Typical Mistakes with `UPDATE`

1. Forgetting the `WHERE` clause

sql

UPDATE users
SET is_active = FALSE;

This disables all users, not just one. To avoid this, many developers run with a habit:

Always write WHERE first
Then fill in the condition
Only then write the SET part

2. Wrong condition

sql

UPDATE users
SET is_active = FALSE
WHERE country <> 'France';

If you meant = instead of <>, you just deactivated everyone except French users.

To minimize this risk, test conditions with SELECT:

sql

SELECT id, username, country
FROM users
WHERE country <> 'France';

3. Updating primary keys carelessly

Changing primary keys can break foreign keys or relationships.

sql

UPDATE users
SET id = 999
WHERE id = 1;

This is usually a bad idea unless you really understand all related tables. In many applications, never update primary keys.

Summary

UPDATE changes the values of existing rows in a table.
The syntax is:

sql

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

Without WHERE, you update every row, which is often a dangerous mistake.
You can use expressions, functions, and subqueries to calculate new values.
Use SELECT first and transactions to keep updates safe.
In some databases, RETURNING lets you see the changed rows immediately.

With these patterns and safety habits you can confidently modify data in your backend’s database.