UPDATE - Modifying Existing Data

Introduction

Data changes. Customers update their email addresses, product prices increase, users change their passwords. The UPDATE statement modifies existing rows in your database.

Warning: UPDATE is powerful and permanent. Always use WHERE to avoid updating every row!

The UPDATE Statement

Basic Syntax

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

Your First UPDATE

-- Change Alice's age
UPDATE users
SET age = 29
WHERE name = 'Alice Smith';

Result:

UPDATE 1

One row was updated.

Verify the Update

SELECT * FROM users WHERE name = 'Alice Smith';

Output:

 id |    name      |        email         | age
----+--------------+----------------------+-----
  1 | Alice Smith  | alice@example.com    |  29  ← Changed from 28

Updating Multiple Columns

UPDATE users
SET
    age = 35,
    email = 'bob.jones@newdomain.com'
WHERE name = 'Bob Jones';

Updates both age and email in one statement.

The WHERE Clause is Critical

Without WHERE - Updates ALL Rows

-- ⚠️ DANGEROUS!
UPDATE users SET age = 99;

Result: EVERY user's age is now 99!

SELECT name, age FROM users;

Output:

     name      | age
---------------+-----
 Alice Smith   |  99  ← Everyone is 99!
 Bob Jones     |  99
 Carol White   |  99
 ...

Always use WHERE unless you intentionally want to update all rows!

Best Practice: Test with SELECT First

Before updating, verify which rows will be affected:

-- 1. Test with SELECT
SELECT * FROM users WHERE age < 25;

-- 2. If results look right, UPDATE
UPDATE users SET age = age + 1 WHERE age < 25;

Updating Based on Current Values

Increment a Value

-- Give everyone a birthday (add 1 to age)
UPDATE users
SET age = age + 1
WHERE age IS NOT NULL;

Concatenate Strings

-- Add suffix to all names
UPDATE users
SET name = name || ' (VIP)'
WHERE age > 30;

Calculations

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

-- Increase all prices by 10%
UPDATE products
SET price = price * 1.10;

Updating Multiple Rows

-- Update all users from example.com domain
UPDATE users
SET email = REPLACE(email, 'example.com', 'newdomain.com')
WHERE email LIKE '%@example.com';

Result:

UPDATE 10

10 rows were updated.

Conditional Updates with CASE

-- Give raises based on age
UPDATE users
SET age = CASE
    WHEN age < 25 THEN age + 1
    WHEN age >= 25 AND age < 35 THEN age + 2
    ELSE age + 3
END;

Different update logic based on current values.

RETURNING Clause

See What Was Updated

UPDATE users
SET age = age + 1
WHERE name LIKE 'C%'
RETURNING *;

Output:

 id |     name     |        email         | age
----+--------------+----------------------+-----
  3 | Carol White  | carol@example.com    |  23  ← Updated

Shows the updated row(s) immediately.

Return Specific Columns

UPDATE users
SET email = 'updated@example.com'
WHERE id = 5
RETURNING id, name, email;

Common Patterns

Pattern 1: Update Timestamp

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    updated_at TIMESTAMP
);

-- Update post and set timestamp
UPDATE posts
SET
    content = 'Updated content here',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

Pattern 2: Toggle Boolean

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    description TEXT,
    completed BOOLEAN DEFAULT false
);

-- Mark task as complete
UPDATE tasks
SET completed = true
WHERE id = 5;

-- Toggle completion status
UPDATE tasks
SET completed = NOT completed
WHERE id = 5;

Pattern 3: Update from Another Table

-- Update users based on data in another table
UPDATE users
SET age = user_ages.new_age
FROM user_ages
WHERE users.id = user_ages.user_id;

Handling NULL Values

Setting to NULL

-- Clear email for a user
UPDATE users
SET email = NULL
WHERE name = 'Test User';

Updating NULL Values

-- Set default age for users without one
UPDATE users
SET age = 18
WHERE age IS NULL;

Safety Tips

1. Use Transactions

BEGIN;

UPDATE users SET age = 100 WHERE name = 'Test';

-- Check if it looks right
SELECT * FROM users WHERE name = 'Test';

-- If good:
COMMIT;

-- If wrong:
ROLLBACK;

2. Backup Before Mass Updates

-- Create backup
CREATE TABLE users_backup AS SELECT * FROM users;

-- Do updates
UPDATE users SET age = age + 1;

-- If something went wrong, restore:
-- DELETE FROM users;
-- INSERT INTO users SELECT * FROM users_backup;

3. Use WHERE with Primary Keys

-- Safe: Updates exactly one row
UPDATE users SET email = 'new@example.com' WHERE id = 5;

-- Risky: Might update multiple rows
UPDATE users SET email = 'new@example.com' WHERE name = 'John';

4. Limit Updates (MySQL)

-- Update at most 10 rows
UPDATE users SET active = false LIMIT 10;

(PostgreSQL doesn't support LIMIT in UPDATE, but you can use subqueries)

Common Mistakes

Mistake 1: Forgetting WHERE

-- ❌ Updates EVERY row
UPDATE users SET age = 25;

-- ✅ Updates specific rows
UPDATE users SET age = 25 WHERE name = 'Alice Smith';

Mistake 2: Wrong WHERE Condition

-- Intended to update user with ID 5
-- But used = instead of IN
UPDATE users SET age = 30 WHERE id = 5,6,7;  -- ❌ Syntax error

-- ✅ Correct
UPDATE users SET age = 30 WHERE id IN (5, 6, 7);

Mistake 3: Not Testing First

-- ❌ Directly updating
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';

-- ✅ Test first
SELECT COUNT(*) FROM users WHERE last_login < '2023-01-01';
-- If count looks reasonable, then UPDATE

Performance Considerations

Indexes Help WHERE Clauses

-- Create index for faster updates
CREATE INDEX idx_users_email ON users(email);

-- This UPDATE will be faster
UPDATE users SET age = 30 WHERE email = 'test@example.com';

Updating Many Rows

-- Updating millions of rows at once can lock the table
-- Consider batching:

UPDATE users SET active = false
WHERE id IN (
    SELECT id FROM users WHERE last_login < '2023-01-01' LIMIT 1000
);

Practice Exercises

Exercise 1

Update Carol White's email to 'carol.white@newmail.com'.

UPDATE users
SET email = 'carol.white@newmail.com'
WHERE name = 'Carol White';

Exercise 2

Increase the age of all users over 30 by 1.

UPDATE users
SET age = age + 1
WHERE age > 30;

Exercise 3

Set the email to NULL for all users under 20.

UPDATE users
SET email = NULL
WHERE age < 20;

Exercise 4

Update user with ID 3, set age to 40, and return the updated row.

UPDATE users
SET age = 40
WHERE id = 3
RETURNING *;

Key Takeaways

• ✅ UPDATE modifies existing rows
• ✅ SET specifies new values
• ✅ WHERE determines which rows to update
• ✅ Without WHERE, all rows are updated
• ✅ Test with SELECT before UPDATE
• ✅ RETURNING shows updated data
• ✅ Use transactions for safety

Next Steps

You can now modify existing data! But sometimes you need to remove data entirely. In the next lesson, we'll learn DELETE—how to remove rows from your database.

Stay careful with DELETE, just like UPDATE!