UPDATE

Overview

The UPDATE statement is used to modify existing records in a table. It allows you to change column values for rows that match specified conditions.

Important: Always use WHERE with UPDATE to avoid updating all rows in the table accidentally.

Basic Syntax

The UPDATE statement syntax:

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

Update Single Column

UPDATE Customers
SET email = 'newemail@example.com'
WHERE customer_id = 101;

Update Multiple Columns

UPDATE Customers
SET 
    name = 'John Smith',
    email = 'johnsmith@example.com',
    city = 'Boston'
WHERE customer_id = 101;

Update Multiple Rows

Use WHERE to update multiple rows that match the condition:

UPDATE Products
SET price = price * 0.9  -- 10% discount
WHERE category = 'Electronics';

Examples

Example 1: Update Specific Record

UPDATE Customers
SET phone = '555-1234'
WHERE customer_id = 101;

Example 2: Update Based on Condition

UPDATE Products
SET discount = 15
WHERE price BETWEEN 100 AND 500;

Example 3: Update with Calculations

UPDATE Orders
SET total_amount = quantity * unit_price
WHERE order_id = 1;

Example 4: Update Using JOIN

UPDATE Orders o
SET o.status = 'Shipped'
FROM Customers c
WHERE o.customer_id = c.customer_id
  AND c.city = 'New York';

-- Alternative syntax (MySQL)
UPDATE Orders o
INNER JOIN Customers c ON o.customer_id = c.customer_id
SET o.status = 'Shipped'
WHERE c.city = 'New York';

UPDATE with WHERE Clause

CRITICAL: Always include a WHERE clause unless you intend to update all rows:

-- ⚠️ WARNING: This updates ALL customers!
UPDATE Customers
SET status = 'Active';

-- ✅ Safe: Only updates specific customer
UPDATE Customers
SET status = 'Active'
WHERE customer_id = 101;

Updating with NULL

You can set columns to NULL (if the column allows it):

UPDATE Customers
SET phone = NULL
WHERE customer_id = 101;

Update with Subqueries

You can use subqueries to determine update values:

UPDATE Products
SET category_id = (
    SELECT category_id 
    FROM Categories 
    WHERE category_name = 'Electronics'
)
WHERE product_name = 'Laptop';

Incrementing/Decrementing Values

You can update by adding or subtracting from current values:

-- Increase price by 10%
UPDATE Products
SET price = price * 1.1
WHERE category = 'Electronics';

-- Decrease stock quantity
UPDATE Products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 5;

-- Add to current value
UPDATE Orders
SET total_amount = total_amount + 50
WHERE order_id = 1;

Best Practices

• Always use WHERE: Prevent accidental updates to all rows
• Test with SELECT first: Use SELECT with same WHERE to preview changes
• Use transactions: Wrap updates in transactions for rollback capability
• Backup first: Backup data before major updates
• Update one row at a time: For critical data, update individually
• Verify updates: Use SELECT after UPDATE to confirm changes

Safety Checklist

-- Step 1: Preview what will be updated
SELECT * FROM Customers
WHERE customer_id = 101;

-- Step 2: Test WHERE clause
SELECT COUNT(*) FROM Customers
WHERE customer_id = 101;

-- Step 3: Perform update (in transaction)
BEGIN TRANSACTION;

UPDATE Customers
SET email = 'newemail@example.com'
WHERE customer_id = 101;

-- Step 4: Verify the update
SELECT * FROM Customers
WHERE customer_id = 101;

-- Step 5: Commit or rollback
COMMIT;
-- or ROLLBACK; if something is wrong

Common Use Cases

• Updating customer information (email, address, phone)
• Changing product prices or descriptions
• Updating order status
• Adjusting inventory quantities
• Changing user account settings

Common Mistakes

• Forgetting WHERE clause: Updates all rows in the table
• Wrong WHERE condition: Updates unintended rows
• Not testing first: Making changes without previewing
• No transaction: Can't rollback if something goes wrong

Next Steps

Learn about removing records with DELETE, or explore how to handle NULL Values in updates.