UPDATE Statement

Modify existing data in tables using the UPDATE statement.

12 min read
Beginner

Introduction to UPDATE

So far, you've learned to INSERT data and SELECT it. But what happens when data needs to change? An employee gets a raise, a product price changes, or a customer moves to a new address?

That's where the UPDATE statement comes in! It lets you modify existing data in your database.

A SQL command that modifies existing rows in a table. It specifies which table to update, what changes to make, and which rows to modify.

Basic Syntax:

UPDATE table_name
SET column1 = new_value1,
    column2 = new_value2, ...
WHERE condition;

Let's work with our products table:

products
id
product_name
category
price
stock
last_updated
1LaptopElectronics999.99452024-01-10
2MouseElectronics29.991202024-01-10
3Desk ChairFurniture199.99302024-01-10
4MonitorElectronics349.99602024-01-10
5Desk LampFurniture39.99852024-01-10

⚠️ CRITICAL WARNING: Always use a WHERE clause with UPDATE! Without it, every row in the table will be updated. We'll see examples of this danger shortly.

Updating a Single Column

Let's say the Laptop price increased to $1,099.99. Here's how to update it:

sql
UPDATE products
SET price = 1099.99
WHERE id = 1;

The database confirms that 1 row was changed. Let's verify:

sql
SELECT id, product_name, price
FROM products
WHERE id = 1;

What happened:

  1. UPDATE products - specifies which table
  2. SET price = 1099.99 - specifies the new value
  3. WHERE id = 1 - crucially important! - only updates the row where id = 1

Without the WHERE clause, all products would have been set to $1,099.99!

Updating Multiple Columns

You can update multiple columns at once by separating them with commas:

sql
UPDATE products
SET price = 34.99,
    stock = 100,
    last_updated = '2024-02-07'
WHERE id = 2;
sql
SELECT id, product_name, price, stock, last_updated
FROM products
WHERE id = 2;

All three columns were updated in a single statement! This is more efficient than running three separate UPDATE statements.

Updating Multiple Rows

You can update multiple rows at once if they match the WHERE condition:

sql
-- Give all Electronics a 10% discount
UPDATE products
SET price = price * 0.90
WHERE category = 'Electronics';

Let's see the results:

sql
SELECT product_name, category, price
FROM products;

Notice that:

  • Laptop, Mouse, and Monitor (Electronics) were discounted by 10%
  • Desk Chair and Desk Lamp (Furniture) weren't affected
  • We used price = price * 0.90 to calculate the new price based on the current value

Updating with Calculations

UPDATE can use the current column values in calculations:

sql
-- Increase all stock quantities by 50
UPDATE products
SET stock = stock + 50;

Notice: This UPDATE has no WHERE clause, which means it affects all rows. This is intentional here - we want to increase stock for all products. But be very careful with this!

sql
SELECT product_name, stock FROM products;

More calculation examples:

sql
-- Reduce stock by 10 for low-stock items
UPDATE products
SET stock = stock - 10
WHERE stock < 100;

Updating with String Functions

You can also update text columns using string functions:

sql
-- Convert all product names to uppercase
UPDATE products
SET product_name = UPPER(product_name)
WHERE category = 'Electronics';
sql
SELECT product_name, category FROM products;

Electronics products are now in uppercase, while Furniture products remain unchanged!

The Danger of Missing WHERE Clause

This is the #1 mistake with UPDATE statements. Let's see what happens when you forget WHERE:

⚠️ DANGEROUS EXAMPLE - DON'T DO THIS:

-- OOPS! Forgot WHERE clause
UPDATE products
SET price = 0.00;

Result: Every single product in the table now has price = 0.00!

products (After Disaster)
id
product_name
price
1Laptop0.00
2Mouse0.00
3Desk Chair0.00
4Monitor0.00
5Desk Lamp0.00

Everything is now free! This is a catastrophic mistake in production databases.

How to prevent this:

  1. Always write WHERE first (before SET) when composing queries
  2. Test with SELECT - run a SELECT with your WHERE clause to see which rows would be affected
  3. Use transactions (covered in advanced lessons) to roll back mistakes
  4. Double-check before hitting Enter!

Best Practice: Before running an UPDATE, run a SELECT with the same WHERE clause to see what will be affected:

-- 1. First, check what will be updated
SELECT * FROM products WHERE id = 1;

-- 2. If that looks right, run the UPDATE
UPDATE products SET price = 1099.99 WHERE id = 1;

UPDATE with Complex Conditions

You can use any WHERE condition you've learned, including multiple conditions:

sql
-- Update cheap Electronics items
UPDATE products
SET stock = stock + 20
WHERE category = 'Electronics'
  AND price < 50;
sql
SELECT product_name, category, price, stock
FROM products
WHERE category = 'Electronics';

Only MOUSE matched both conditions (Electronics AND price < 50), so only its stock was increased.

Checking Update Results

SQL provides feedback after UPDATE statements:

UPDATE Feedback Messages
Message
Meaning
`Query OK, 3 rows affected`3 rows were successfully updated
`Query OK, 0 rows affected`No rows matched the WHERE condition
`Rows matched: 5 Changed: 3`5 rows matched WHERE, but only 3 actually changed

Why might "Rows matched" differ from "Changed"?

If you update a column to the same value it already has, it counts as "matched" but not "changed."

sql
UPDATE products
SET price = 989.99
WHERE id = 1;  -- Laptop already has price 989.99

The row was found (matched), but no actual change was made because the price was already 989.99.

Practical Examples

Example 1: Price adjustment

sql
-- Raise prices of Furniture by 5%
UPDATE products
SET price = price * 1.05,
    last_updated = CURRENT_DATE
WHERE category = 'Furniture';

Example 2: Inventory correction

sql
-- Mark out-of-stock items
UPDATE products
SET stock = 0
WHERE stock < 10;

Example 3: Bulk category change

sql
-- Recategorize based on price
UPDATE products
SET category = 'Premium'
WHERE price > 500;

Common Mistakes to Avoid

Don't make these mistakes:

  1. Forgetting WHERE clause: Updates every row! ❌

    • Always include WHERE (unless you really want to update all rows)

  2. Wrong WHERE condition: Updates wrong rows ❌

    • Test with SELECT first: SELECT * FROM products WHERE id = 1;

  3. Missing commas between columns: Syntax error ❌

    • SET price = 99.99, stock = 50 ✓ (comma required)

  4. Setting wrong data types: SET stock = 'fifty' ❌

    • SET stock = 50 ✓ (stock is a number)

  5. Forgetting quotes around strings: SET category = Electronics ❌

    • SET category = 'Electronics' ✓

  6. Not verifying results: Always check with SELECT after UPDATE! ❌

    • SELECT * FROM products WHERE id = 1; ✓

Key Takeaways

What you learned:

✅ UPDATE modifies existing data in tables ✅ SET specifies which columns to change and their new values ✅ WHERE determines which rows to update (CRITICAL!) ✅ You can update one or multiple columns in a single statement ✅ You can update one or multiple rows at once ✅ You can use calculations with current values (price = price * 1.1) ✅ Always test with SELECT first to see what will be affected ✅ Missing WHERE updates all rows - be extremely careful!

Coming up next: We'll learn the DELETE statement to remove rows from tables. Like UPDATE, it requires careful use of WHERE clauses!

Practice Exercise: Try these UPDATE statements:

  1. Increase the price of product with id = 3 by $20
  2. Set all Electronics products to have stock = 100
  3. Give a 15% discount to products with stock > 100
  4. Update the last_updated date for all products
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.