How to Update Data in SQL

What you’ll build or solve

You’ll modify one or more columns in existing rows using an UPDATE statement.

When this approach works best

Updating data works best when you:

• Correct outdated or incorrect values
• Change the status of records, such as marking orders as shipped
• Adjust prices, flags, or timestamps for existing rows

For example, you might update a user’s email address, set an order’s status to completed, or apply a discount to selected products.

This is a bad idea if you need to create new rows. Use INSERT when adding new records.

Prerequisites

• Access to the database
• Permission to update the target table
• Basic SQL knowledge

Step-by-step instructions

Step 1: Update rows with UPDATE ... SET ... WHERE

Use UPDATE, define the new values with SET, and filter rows with WHERE.

Update a single column

UPDATE users
SET email='alex.new@example.com'
WHERE id=1;

Update multiple columns in the same row

UPDATE users
SET username='alex_new',
    email='alex.new@example.com'
WHERE id=1;

The WHERE clause determines which rows are affected. Without it, every row in the table is updated.

What to look for

• Always include a WHERE clause unless you intentionally want to update all rows
• Without WHERE, for example:

UPDATE products
SET discount=0;

every row changes.

• You can use more complex conditions in WHERE, including joins in supported systems, such as:

UPDATE orders o
JOIN users uON o.user_id= u.id
SET o.status='priority'
WHERE u.is_premium=TRUE;

• Preview affected rows first with:

SELECT*FROM usersWHERE id=1;

• Match assigned values to the column data types

Examples you can copy

Example 1: Mark an order as shipped

UPDATE orders
SET status='shipped'
WHERE id=1001;

Example 2: Apply a discount to a category

UPDATE products
SET price= price*0.9
WHERE category='electronics';

Example 3: Deactivate old accounts

UPDATE users
SET is_active=FALSE
WHERE last_login<'2023-01-01';

Common mistakes and how to fix them

Mistake 1: Forgetting the WHERE clause

What you might do:

UPDATE users
SET is_active=FALSE;

Why it breaks expectations: Every row in the table is updated.

Correct approach:

UPDATE users
SET is_active=FALSE
WHERE last_login<'2023-01-01';

Always filter rows unless you truly want a full-table update.

Mistake 2: Using incorrect data types

What you might do:

UPDATE products
SET price='free'
WHERE id=1;

Why it fails: If price is numeric, assigning text causes a type error.

Correct approach:

UPDATE products
SET price=0
WHERE id=1;

Use values that match the column definition.

Mistake 3: Updating rows that do not match the condition

What you might do:

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

Why nothing changes: No row matches the condition.

Correct approach: Check first:

SELECT idFROM usersWHERE id=9999;

Then update valid records.

Troubleshooting

• If you see “permission denied,” confirm you have UPDATE privileges.
• If zero rows are affected, verify your WHERE condition.
• If you see a constraint error, check foreign keys and unique constraints.
• If too many rows were updated, restore from backup or transaction log if available.

Quick recap

• Use UPDATE ... SET ... WHERE to modify existing rows
• Update one or multiple columns in a single statement
• Always use WHERE unless you want to affect all rows
• Preview affected rows with SELECT first
• Match values to column data types and constraints