Skip to main content

UPDATE

Learning Focus

Use this lesson to understand UPDATE with practical syntax and examples.

Concept Overview​

What Is the UPDATE Statement?​

The UPDATE statement in MySQL is used to modify existing records in a table. Unlike the INSERT statement that adds new records, UPDATE changes data in existing rows based on a specified condition.

Why Is It Important?​

  • Enables data correction without deleting and re-adding rows
  • Essential for maintaining accurate, up-to-date records
  • Frequently used in real-world applications like editing user profiles, updating inventory, or modifying order statuses

Where It Fits in the Database Workflow​

UPDATE is part of the Data Manipulation Language (DML), alongside:

  • SELECT - to retrieve data
  • INSERT - to add data
  • DELETE - to remove data

These form the CRUD operations: Create, Read, Update, Delete.

Basic Syntax & Rules​

Syntax​

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

Explanation of Parameters​

  • table_name: Name of the table where data will be updated
  • SET: Keyword followed by column-value pairs to update
  • WHERE: Critical to define which rows to update; without it, all rows will be updated

Notes and Considerations​

  • Always use a WHERE clause unless you intend to update every row
  • Can combine with JOIN to update based on another table
  • Can include subqueries in SET or WHERE
  • A LIMIT clause can restrict the number of rows updated

Step-by-Step Examples​

Example 1: Basic Update​

Table: employees

idnamedepartmentsalary
1AliceSales50000
2BobMarketing52000

SQL:

-- Increase salary for Alice by 10%
UPDATE employees
SET salary = salary * 1.10
WHERE name = 'Alice';

Result:

idnamedepartmentsalary
1AliceSales55000
2BobMarketing52000

Example 2: Update Multiple Columns​

-- Promote Bob to Sales and increase salary
UPDATE employees
SET department = 'Sales',
    salary = 58000
WHERE name = 'Bob';

Example 3: Update With a Join​

Tables:

orders

order_idcustomer_idstatus
1011pending

customers

idnameis_active
1Sarah0
-- Cancel orders for inactive customers
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.status = 'cancelled'
WHERE c.is_active = 0;

Practical Use Cases​

1. User Profile Updates​

UPDATE users
SET email = 'newemail@example.com'
WHERE id = 5;

2. Inventory Management​

UPDATE products
SET stock = stock - 1
WHERE product_id = 101;

3. Employee Promotions​

UPDATE employees
SET position = 'Manager', salary = salary * 1.2
WHERE performance_rating = 'Excellent';

4. Subscription Status​

UPDATE subscriptions
SET status = 'expired'
WHERE end_date < CURDATE();

5. Error Correction in Data Entry​

UPDATE customers
SET phone_number = '123-456-7890'
WHERE customer_id = 42;

Common Mistakes & Troubleshooting​

1. Omitting the WHERE Clause​

UPDATE employees
SET salary = 0; -- Updates all salaries to 0 (likely unintended)

Fix: Always double-check your WHERE clause.

2. Incorrect Conditions​

UPDATE users
SET is_active = 1
WHERE last_login = NULL; -- Always false

Fix: Use IS NULL instead of = NULL

3. Updating Primary Key by Mistake​

Changing a primary key might cause referential issues in other tables.

Fix: Avoid modifying primary keys unless necessary and foreign key constraints are accounted for.

4. Silent Failures​

Query runs but updates 0 rows - often due to wrong WHERE clause.

Tip: Use SELECT with the same WHERE to preview rows affected.

Best Practices​

  • Always Backup Before Mass Updates

  • Run SELECT first:

    SELECT * FROM employees WHERE salary < 50000;

  • Use Transactions for bulk updates:

    START TRANSACTION;
    UPDATE ...;
    COMMIT;

  • Limit Updates for testing:

    UPDATE users SET is_active = 1 WHERE last_login IS NOT NULL LIMIT 10;

  • Avoid Updating Unindexed Columns in WHERE: slows performance

Hands-On Practice​

Sample Data Setup​

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Alice', 'Sales', 50000),
(2, 'Bob', 'Marketing', 52000),
(3, 'Clara', 'IT', 60000);

Exercise 1: Simple Update​

Task: Increase Bob's salary by 5%.

-- Your Query Here

Answer:

UPDATE employees
SET salary = salary * 1.05
WHERE name = 'Bob';

Exercise 2: Update Based on Condition​

Task: Move employees with salary > 55000 to "Executive" department.

Answer:

UPDATE employees
SET department = 'Executive'
WHERE salary > 55000;

Exercise 3: Conditional + Multiple Column Update​

Task: Give a 10% raise and move to "Leadership" for employees in IT department.

Answer:

UPDATE employees
SET salary = salary * 1.10,
    department = 'Leadership'
WHERE department = 'IT';

Connection to Other Concepts​

Prerequisite Knowledge​

  • SELECT statements
  • Data types and table structure
  • Primary keys and indexes
  • INSERT - add data
  • DELETE - remove data
  • JOIN - for cross-table updates
  • TRANSACTION - rollback/commit changes safely

What to Learn Next?​

  • MERGE (in other RDBMS)
  • Stored Procedures for complex updates
  • Trigger-based automatic updates
  • Data validation and constraints

Concept Map​

flowchart LR
    A[Schema Context] --> B[UPDATE]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls​

PitfallConsequencePrevention
Executing queries without validating sample rowsLogic errors reach production data or reportsStart with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behaviorAggregations and filters return misleading resultsTest with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schemaQueries fail due to missing columns/indexesVerify structure with DESCRIBE table_name; and adapt query design

Quick Reference​

UPDATE table_name SET column_name = new_value WHERE id = 1;
UPDATE users SET status = "inactive" WHERE last_login < "2024-01-01";

What's Next​