MySql - Updating records

Updating Records (UPDATE) in MySQL

In relational databases like MySQL, the UPDATE statement is used to modify existing records in a table. This is a fundamental operation used for maintaining data consistency, correcting values, updating statuses, or modifying timestamps. Understanding how to effectively use the UPDATE command—including conditions, joins, subqueries, and best practices—is crucial for database administrators and developers. This document provides a comprehensive explanation of the UPDATE statement in MySQL with usage examples, syntax breakdowns, and performance considerations.

1. Basic Syntax of UPDATE Statement

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

Without a WHERE clause, all rows in the table will be updated.

Example:

UPDATE employees
SET salary = salary + 1000
WHERE department = 'Sales';

2. Updating a Single Column

To change the value of one column in a specific row:

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

3. Updating Multiple Columns

You can update more than one column in the same query:

UPDATE products
SET price = price * 1.10, stock = stock - 5
WHERE category = 'Electronics';

4. Conditional Updates with WHERE Clause

4.1 Using Comparison Operators

UPDATE orders
SET status = 'shipped'
WHERE order_date < '2025-07-01';

4.2 Using IN, BETWEEN, AND, OR

UPDATE employees
SET bonus = 500
WHERE department IN ('HR', 'Finance')
AND hire_date BETWEEN '2020-01-01' AND '2023-12-31';

4.3 Using LIKE for Pattern Matching

UPDATE customers
SET newsletter_subscribed = 1
WHERE email LIKE '%@gmail.com';

5. Updating Records Without WHERE Clause

Be extremely cautious when omitting the WHERE clause—it affects all rows.

UPDATE settings
SET is_active = 0;

This will deactivate all settings in the table.

6. Using Expressions and Functions in UPDATE

6.1 String Functions

UPDATE users
SET username = UPPER(username)
WHERE id < 1000;

6.2 Mathematical Expressions

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 5001;

6.3 Date Functions

UPDATE subscriptions
SET renewal_date = DATE_ADD(renewal_date, INTERVAL 1 YEAR)
WHERE status = 'active';

7. UPDATE with JOIN

You can update records in one table based on matching data in another table using a JOIN.

7.1 INNER JOIN

UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.discount = 10
WHERE c.membership = 'gold';

7.2 LEFT JOIN

UPDATE employees e
LEFT JOIN departments d ON e.department_id = d.id
SET e.department_name = d.name
WHERE d.name IS NOT NULL;

8. Subqueries in UPDATE Statements

8.1 Using Subquery in SET

UPDATE employees
SET salary = (
    SELECT AVG(salary)
    FROM employees
    WHERE department = 'Engineering'
)
WHERE department = 'Engineering';

8.2 Using Subquery in WHERE

UPDATE products
SET status = 'discontinued'
WHERE id NOT IN (
    SELECT product_id
    FROM order_items
);

9. Limiting Rows with LIMIT Clause

In MySQL, you can limit how many rows are updated using LIMIT:

UPDATE logs
SET status = 'archived'
ORDER BY created_at ASC
LIMIT 100;

10. ORDER BY in UPDATE

Use ORDER BY to control the order of updates (especially when using LIMIT):

UPDATE users
SET points = points + 10
ORDER BY last_login DESC
LIMIT 50;

11. Safe Updates Mode

MySQL has a “safe updates” mode that prevents accidental full-table updates.

SET SQL_SAFE_UPDATES = 1;

In this mode, you must include a WHERE clause with a key or use LIMIT.

12. Using Transactions with UPDATE

Wrap UPDATEs inside a transaction to ensure atomicity:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

If something fails, you can use ROLLBACK instead of COMMIT.

13. Updating from a SELECT Statement

You can use a SELECT in SET to reference values from other rows:

UPDATE employees e
SET e.salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department = e.department
);

14. Tracking Update History

Add updated_at timestamp columns to monitor record changes:

ALTER TABLE users
ADD updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

This automatically records the time of the last update.

15. Updating JSON Fields

MySQL supports updating JSON fields using JSON_SET():

UPDATE logs
SET metadata = JSON_SET(metadata, '$.status', 'archived')
WHERE id = 123;

16. Updating NULL Values

16.1 Detecting NULLs

UPDATE users
SET last_login = NOW()
WHERE last_login IS NULL;

16.2 Setting NULLs

UPDATE payments
SET refunded_at = NULL
WHERE refund_status = 'pending';

17. Performance Tips for UPDATE

• Always use WHERE clause to avoid full table updates unless intentional.
• Make sure updated columns are indexed if involved in WHERE.
• Avoid complex joins or subqueries on large datasets—test first.
• Use LIMIT to break massive updates into smaller batches.
• Use transactions when updating multiple tables together.

18. Error Handling

Check for errors or affected rows using client libraries (Node.js, Python, etc.):

// Node.js Example
connection.query('UPDATE users SET active = 0 WHERE last_login < NOW() - INTERVAL 1 YEAR', function (error, results) {
    if (error) throw error;
    console.log('Rows affected:', results.affectedRows);
});

19. Real-World Examples

19.1 Reset Login Attempts

UPDATE users
SET login_attempts = 0
WHERE login_attempts > 0;

19.2 Deactivating Expired Subscriptions

UPDATE subscriptions
SET is_active = 0
WHERE expiry_date < CURDATE();

19.3 Marking Orders as Completed

UPDATE orders
SET status = 'completed'
WHERE shipped_date IS NOT NULL
AND status != 'completed';

The UPDATE statement in MySQL is powerful and essential for modifying data in a table. Whether updating a single value, multiple columns, or using complex logic involving joins and subqueries, understanding the syntax and best practices ensures accuracy and efficiency. Always use WHERE clauses, test your statements with SELECT first, and consider wrapping critical updates in transactions to avoid data corruption. By mastering the UPDATE command, database administrators and developers can ensure data integrity and maintain accurate information across systems.