SQL UPDATE — Change Rows Safely

Table Hierarchy & Schema

Database → tables

CREATE TABLE (reference)json

1CREATE TABLE users (
2  user_id   INTEGER PRIMARY KEY,
3  email     VARCHAR(255) NOT NULL UNIQUE,
4  is_active BOOLEAN NOT NULL DEFAULT TRUE
5);
6
7CREATE TABLE orders (
8  order_id     INTEGER PRIMARY KEY,
9  customer_id  INTEGER NOT NULL,
10  status       VARCHAR(20) NOT NULL DEFAULT 'pending',
11  total_amount DECIMAL(10, 2) NOT NULL,
12  FOREIGN KEY (customer_id) REFERENCES users (user_id)
13);

Entity-Relationship Diagram

users ↔ orders ER

UPDATE Flow

WHERE narrows rows before SET applies

Sample Data

usersjson

1| user_id | email               | is_active |
2|---------|---------------------|-----------|
3| 1       | [email protected]   | true      |
4| 2       | [email protected]     | false     |
5| 3       | [email protected]   | true      |

orders (before update)json

1| order_id | customer_id | status   | total_amount |
2|----------|-------------|----------|--------------|
3| 1001     | 1           | paid     | 49.99        |
4| 1002     | 1           | shipped  | 12.50        |
5| 1003     | 3           | pending  | 8.00         |

Basic UPDATE

Mark Carol's pending order as paidjson

1UPDATE orders
2SET status = 'paid'
3WHERE order_id = 1003;

Update Multiple Columns

Adjust amount and status on one orderjson

1UPDATE orders
2SET status = 'shipped', total_amount = 50.00
3WHERE order_id = 1001;

UPDATE With a Filter Pattern

Mark all of Alice's orders as shippedjson

1UPDATE orders
2SET status = 'shipped'
3WHERE customer_id = 1;

UPDATE users Table

Reactivate Bobjson

1UPDATE users
2SET is_active = TRUE
3WHERE user_id = 2;

Danger: Missing WHERE

Never run this in productionjson

1UPDATE orders SET status = 'cancelled';
2-- Updates EVERY order row

Always preview first

Run a SELECT … WHERE … with the same condition before UPDATE to see affected rows.

Constraints Reminder

Constraint	Role on UPDATE
PRIMARY KEY (user_id / order_id)	Uniquely identifies each row; join anchor
FOREIGN KEY (customer_id)	Order must reference a valid users.user_id
NOT NULL	Required columns cannot be missing on INSERT/UPDATE
UNIQUE (email)	No duplicate emails in users
DEFAULT (status, is_active)	Value applied when column omitted on INSERT

Common Mistakes

Forgetting WHERE → mass update of entire table.
Setting FK column to invalid parent id.
Using = NULL in WHERE instead of IS NULL.

Practice Prompts

Write SELECT then UPDATE to change order 1002 status to delivered.
Deactivate all users who have is_active = false (already false for Bob — idempotent update).
Increase total_amount by 10% for orders over 20.00.

Try These Tools

SQL Validator

Validate UPDATE statements

Open tool

SQL Query Explainer

Understand UPDATE scope

Open tool

Continue Learning

INSERT INTO

Add rows before updating them

DELETE

Remove rows when update is not enough

Frequently Asked Questions

What does UPDATE do?

Changes column values in existing rows. Syntax: UPDATE table SET col = value WHERE condition.

What happens if I omit WHERE?

Every row in the table is updated — almost always a mistake in production.

Can UPDATE violate constraints?

Yes. Setting customer_id to a non-existent user_id fails FK checks. Duplicate email fails UNIQUE.

How do I update multiple columns?

Separate assignments with commas: SET status = paid, total_amount = 50.00.

UPDATE with JOIN?

Some databases allow UPDATE … FROM … JOIN or UPDATE t JOIN … SET. Filter carefully to avoid updating unintended rows.