UPDATE: Modifying Data in Tables

Data changes over time—customers move, prices fluctuate, inventory updates, and mistakes need fixing. UPDATE is the SQL command that modifies existing data in your tables. Whether you're correcting a typo, applying a price increase to all products, or updating a single customer's email, UPDATE gives you precise control. In this lesson, you'll learn how to update single rows, multiple rows, use calculations, and—most importantly—how to avoid accidentally updating the wrong data.

📚 What You'll Learn

• How to update single rows safely
• How to update multiple rows with one statement
• How to use WHERE to target specific rows
• How to update with calculations and expressions
• How to update based on values from other columns
• How to update with JOINs (using data from other tables)
• How to handle NULL values in updates
• Common UPDATE mistakes and how to avoid them
• How to test updates safely before committing

1. Basic UPDATE Syntax

Complete Syntax

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

2. Updating a Single Row

Update by Primary Key (Safest)

-- Update a specific customer by ID
UPDATE customers
SET email = 'newemail@example.com',
    phone = '555-9999'
WHERE customer_id = 42;

-- Only the customer with ID 42 is updated

Update by Unique Column

-- Update customer by email (if email is UNIQUE)
UPDATE customers
SET phone = '555-1234',
    last_name = 'Smith-Johnson'
WHERE email = 'alice@example.com';

-- Only one row affected because email is unique

Verify Before Updating

-- ALWAYS check what you're about to update first!

-- Step 1: SELECT to see what will be affected
SELECT * FROM customers
WHERE customer_id = 42;

-- Step 2: If correct, run the UPDATE
UPDATE customers
SET email = 'newemail@example.com'
WHERE customer_id = 42;

-- Step 3: Verify the change
SELECT * FROM customers
WHERE customer_id = 42;

3. Updating Multiple Rows

You can update many rows at once by using a WHERE clause that matches multiple records. This is powerful but requires extra caution.

Example: Update All Products in a Category

-- Increase price by 10% for all electronics
UPDATE products
SET price = price * 1.10
WHERE category = 'Electronics';

-- Check how many rows will be affected FIRST
SELECT COUNT(*) FROM products WHERE category = 'Electronics';
-- Then run the UPDATE

Example: Mark Old Orders as Archived

-- Archive orders older than 1 year
UPDATE orders
SET status = 'archived'
WHERE order_date < DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
  AND status = 'completed';

Example: Update Based on Range

-- Give discount flag to products in a price range
UPDATE products
SET is_discounted = TRUE
WHERE price BETWEEN 10.00 AND 50.00
  AND stock_quantity > 100;

4. Using Calculations and Expressions

Mathematical Operations

-- Increase all salaries by 5%
UPDATE employees
SET salary = salary * 1.05;

-- Add 10 to all stock quantities
UPDATE products
SET stock_quantity = stock_quantity + 10
WHERE category = 'Office Supplies';

-- Apply a $5 discount to all items
UPDATE products
SET price = price - 5.00
WHERE price > 50.00;

String Operations

-- Uppercase all product names
UPDATE products
SET name = UPPER(name);

-- Add prefix to SKU codes
UPDATE products
SET sku = CONCAT('PROD-', sku)
WHERE sku NOT LIKE 'PROD-%';

-- Trim whitespace from emails
UPDATE customers
SET email = TRIM(email);

Date Operations

-- Extend all subscriptions by 30 days
UPDATE subscriptions
SET end_date = DATE_ADD(end_date, INTERVAL 30 DAY)
WHERE status = 'active';

-- Update last_login to now
UPDATE users
SET last_login = CURRENT_TIMESTAMP
WHERE user_id = 123;

5. Updating Based on Other Columns

You can set a column's value based on other columns in the same row.

Example: Calculate Total from Components

-- Calculate final price with discount
UPDATE products
SET final_price = price - (price * discount_percent / 100)
WHERE discount_percent > 0;

-- Calculate order total from quantity and unit price
UPDATE order_items
SET line_total = quantity * unit_price;

Example: Conditional Updates

-- Set availability based on stock
UPDATE products
SET is_available = CASE 
    WHEN stock_quantity > 0 THEN TRUE 
    ELSE FALSE 
END;

-- Set shipping cost based on weight
UPDATE orders
SET shipping_cost = CASE
    WHEN total_weight < 1 THEN 5.00
    WHEN total_weight < 5 THEN 10.00
    WHEN total_weight < 10 THEN 15.00
    ELSE 20.00
END;

6. Working with NULL Values

Setting Values to NULL

-- Clear phone numbers (set to NULL)
UPDATE customers
SET phone = NULL
WHERE phone = 'N/A' OR phone = '';

-- Remove optional dates
UPDATE projects
SET completion_date = NULL
WHERE status = 'cancelled';

Handling NULL in Conditions

-- Update only rows where column is NULL
UPDATE customers
SET country = 'USA'
WHERE country IS NULL;

-- Update only rows where column is NOT NULL
UPDATE products
SET updated_at = CURRENT_TIMESTAMP
WHERE description IS NOT NULL;

-- Replace NULL with default value
UPDATE employees
SET department = 'General'
WHERE department IS NULL;

7. Updating with Data from Other Tables

Sometimes you need to update a table based on values from another table. This requires joining the tables.

Syntax

UPDATE table1
JOIN table2 ON table1.id = table2.foreign_id
SET table1.column = table2.column
WHERE condition;

Example: Update Product Prices from Price List

-- Update products table with new prices from import table
UPDATE products p
JOIN price_updates pu ON p.sku = pu.sku
SET p.price = pu.new_price,
    p.updated_at = CURRENT_TIMESTAMP;

Example: Update Customer Totals from Orders

-- Update lifetime_spent based on actual orders
UPDATE customers c
JOIN (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
) o ON c.customer_id = o.customer_id
SET c.lifetime_spent = o.total_spent;

Example: Copy Category Name to Products

-- Add category name to products (denormalization)
UPDATE products p
JOIN categories c ON p.category_id = c.category_id
SET p.category_name = c.name;

8. Using LIMIT to Control Updates

You can limit how many rows are updated using LIMIT. This is useful for batch processing or testing.

Update First N Rows

-- Update only the first 100 pending orders
UPDATE orders
SET status = 'processing'
WHERE status = 'pending'
ORDER BY order_date ASC
LIMIT 100;

-- Process 50 oldest unprocessed records
UPDATE queue
SET processed = TRUE,
    processed_at = CURRENT_TIMESTAMP
WHERE processed = FALSE
ORDER BY created_at ASC
LIMIT 50;

9. Testing Updates Before Committing

Safe Update Process

-- Step 1: Use SELECT to preview what will be updated
SELECT customer_id, email, phone 
FROM customers
WHERE country = 'Canada';
-- Check: Are these the right rows?

-- Step 2: Count how many rows will be affected
SELECT COUNT(*) 
FROM customers 
WHERE country = 'Canada';
-- Check: Is this number expected?

-- Step 3: Run the UPDATE
UPDATE customers
SET tax_rate = 0.13
WHERE country = 'Canada';

-- Step 4: Verify the change
SELECT customer_id, email, tax_rate 
FROM customers
WHERE country = 'Canada'
LIMIT 10;

Using Transactions (Advanced)

-- Start transaction
START TRANSACTION;

-- Run your UPDATE
UPDATE products
SET price = price * 1.15
WHERE category = 'Electronics';

-- Check the results
SELECT * FROM products WHERE category = 'Electronics';

-- If correct: commit
COMMIT;

-- If wrong: rollback (undo)
-- ROLLBACK;

10. Common Mistakes and How to Avoid Them

Mistake 1: Forgetting WHERE Clause

UPDATE customers
SET email = 'test@example.com';
-- DISASTER! Every customer now has the same email!

UPDATE customers
SET email = 'test@example.com'
WHERE customer_id = 42;

Mistake 2: Wrong Condition

UPDATE products
SET is_available = FALSE
WHERE stock_quantity = 0 OR discontinued = TRUE;
-- This marks too many products as unavailable!

-- Check the logic first with SELECT
SELECT COUNT(*) FROM products
WHERE stock_quantity = 0 AND discontinued = TRUE;

UPDATE products
SET is_available = FALSE
WHERE stock_quantity = 0 AND discontinued = TRUE;

Mistake 3: Not Checking Row Count

-- ✅ Always check how many rows will be affected
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Expected: ~50 rows

-- If count is WAY off (like 5000), don't run the UPDATE!
-- Investigate why before proceeding

UPDATE orders
SET status = 'cancelled'
WHERE status = 'pending' AND created_at < '2020-01-01';

Mistake 4: Overwriting Data Without Backup

-- ✅ Create backup before major update
CREATE TABLE customers_backup AS
SELECT * FROM customers;

-- Now safe to run risky update
UPDATE customers
SET phone = CONCAT('+1-', phone)
WHERE country = 'USA' AND phone NOT LIKE '+%';

-- Verify changes
SELECT * FROM customers LIMIT 10;

-- If wrong, restore from backup
-- DELETE FROM customers;
-- INSERT INTO customers SELECT * FROM customers_backup;

11. UPDATE Best Practices

12. 🎯 Practice Exercises

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(20),
    loyalty_points INT DEFAULT 0,
    account_status VARCHAR(20) DEFAULT 'active'
);

-- Task 1: Update specific customer's email
-- First, check what we're updating
SELECT * FROM customers WHERE customer_id = 101;

-- Run the update
UPDATE customers
SET email = 'new.email@example.com'
WHERE customer_id = 101;

-- Verify
SELECT * FROM customers WHERE customer_id = 101;

-- Task 2: Give all customers 50 bonus points
-- Check current points first
SELECT customer_id, loyalty_points FROM customers LIMIT 5;

-- Update all customers
UPDATE customers
SET loyalty_points = loyalty_points + 50;

-- Verify some customers
SELECT customer_id, loyalty_points FROM customers LIMIT 5;

-- Task 3: Deactivate accounts without email
-- Check how many will be affected
SELECT COUNT(*) FROM customers WHERE email IS NULL;

-- Update them
UPDATE customers
SET account_status = 'inactive'
WHERE email IS NULL;

-- Verify
SELECT customer_id, email, account_status 
FROM customers 
WHERE account_status = 'inactive';

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255),
    cost DECIMAL(10, 2),
    price DECIMAL(10, 2),
    markup_percent INT,
    stock_quantity INT,
    is_available BOOLEAN
);

-- Task 1: Calculate markup percentage
UPDATE products
SET markup_percent = ROUND(((price - cost) / cost * 100), 0)
WHERE cost > 0;  -- Avoid division by zero

-- Verify calculation
SELECT product_id, name, cost, price, markup_percent 
FROM products 
LIMIT 5;

-- Task 2: Increase all prices by 8%
-- Preview the change first
SELECT product_id, name, price, 
       ROUND(price * 1.08, 2) AS new_price
FROM products
LIMIT 5;

-- Apply the increase
UPDATE products
SET price = ROUND(price * 1.08, 2);

-- Task 3: Mark out-of-stock products as unavailable
-- Check how many
SELECT COUNT(*) FROM products WHERE stock_quantity = 0;

-- Update them
UPDATE products
SET is_available = FALSE
WHERE stock_quantity = 0;

-- Verify
SELECT product_id, name, stock_quantity, is_available
FROM products
WHERE stock_quantity = 0;

-- Task 4: Round prices to nearest dollar
UPDATE products
SET price = ROUND(price, 0);

-- Verify
SELECT product_id, name, price FROM products LIMIT 10;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    country VARCHAR(50),
    loyalty_tier VARCHAR(20)  -- bronze, silver, gold
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    subtotal DECIMAL(10, 2),
    discount_amount DECIMAL(10, 2),
    shipping_cost DECIMAL(10, 2),
    total_amount DECIMAL(10, 2)
);

-- Task 1: Free shipping for gold tier customers
-- Preview which orders will be affected
SELECT o.order_id, c.loyalty_tier, o.shipping_cost
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.loyalty_tier = 'gold';

-- Apply free shipping
UPDATE orders o
JOIN customers c ON o.customer_id = c.customer_id
SET o.shipping_cost = 0
WHERE c.loyalty_tier = 'gold';

-- Task 2: 10% discount for silver tier
-- Preview the discount calculation
SELECT o.order_id, o.subtotal, 
       ROUND(o.subtotal * 0.10, 2) AS new_discount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.loyalty_tier = 'silver';

-- Apply the discount
UPDATE orders o
JOIN customers c ON o.customer_id = c.customer_id
SET o.discount_amount = ROUND(o.subtotal * 0.10, 2)
WHERE c.loyalty_tier = 'silver';

-- Task 3: Recalculate all order totals
-- Preview calculation
SELECT order_id, subtotal, discount_amount, shipping_cost,
       (subtotal - discount_amount + shipping_cost) AS calculated_total,
       total_amount AS current_total
FROM orders
LIMIT 5;

-- Update all totals
UPDATE orders
SET total_amount = subtotal - discount_amount + shipping_cost;

-- Verify a few orders
SELECT order_id, subtotal, discount_amount, shipping_cost, total_amount
FROM orders
LIMIT 10;

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    category VARCHAR(50),
    base_price DECIMAL(10, 2),
    sale_price DECIMAL(10, 2),
    stock_quantity INT,
    is_on_sale BOOLEAN,
    featured_deal BOOLEAN
);

-- Use transaction for all-or-nothing update
START TRANSACTION;

-- Step 1: Electronics - 25% off if stock > 50, else 15% off
UPDATE products
SET sale_price = CASE 
    WHEN stock_quantity > 50 THEN ROUND(base_price * 0.75, 2)
    ELSE ROUND(base_price * 0.85, 2)
END,
is_on_sale = TRUE
WHERE category = 'Electronics';

-- Step 2: Clothing - 30% off if stock > 100, else 20% off
UPDATE products
SET sale_price = CASE 
    WHEN stock_quantity > 100 THEN ROUND(base_price * 0.70, 2)
    ELSE ROUND(base_price * 0.80, 2)
END,
is_on_sale = TRUE
WHERE category = 'Clothing';

-- Step 3: Books - Flat 40% off
UPDATE products
SET sale_price = ROUND(base_price * 0.60, 2),
    is_on_sale = TRUE
WHERE category = 'Books';

-- Step 4: Mark featured deals (40%+ discount)
UPDATE products
SET featured_deal = TRUE
WHERE is_on_sale = TRUE
  AND sale_price <= (base_price * 0.60);

-- Verify the flash sale setup
SELECT 
    category,
    COUNT(*) AS products_on_sale,
    ROUND(AVG((base_price - sale_price) / base_price * 100), 1) AS avg_discount_pct,
    SUM(CASE WHEN featured_deal THEN 1 ELSE 0 END) AS featured_deals
FROM products
WHERE is_on_sale = TRUE
GROUP BY category;

-- Check individual products
SELECT product_id, category, base_price, sale_price,
       ROUND((base_price - sale_price) / base_price * 100, 1) AS discount_pct,
       stock_quantity, featured_deal
FROM products
WHERE is_on_sale = TRUE
ORDER BY category, discount_pct DESC;

-- If everything looks good, commit
COMMIT;

-- If something's wrong, rollback
-- ROLLBACK;

📝 Key Takeaways

• UPDATE modifies existing rows in tables
• ALWAYS use WHERE clause unless updating all rows intentionally
• Test with SELECT first to see what will be affected
• Update by primary key is safest for single rows
• Use calculations and expressions in SET clause
• CASE statements allow conditional updates
• JOIN with other tables to update based on related data
• Use LIMIT for batch processing large updates
• Transactions let you test before committing
• Check affected row count before updating
• Backup data before major updates
• Set columns to NULL using SET column = NULL
• Use IS NULL / IS NOT NULL in WHERE clauses
• Verify changes after every UPDATE
• Never update production without testing first!