How to update rows in a table in MySQL 8

Introduction

MySQL is a powerful, open-source relational database management system. In MySQL 8, updating rows in a database table is a common operation that allows you to modify your data as your requirements change. In this tutorial, we shall delve deeply into the syntax and nuances of the SQL UPDATE statement in MySQL, providing a variety of examples ranging from basic to advanced use-cases. You’ll learn how to perform simple row updates, handle safety features to prevent accidental data loss, work with joins, and more.

Basic Update Syntax

The basic syntax of the UPDATE statement in MySQL is as follows:

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

Let’s start with the most straightforward case: updating a single row.

Example: Suppose you have a table employees and you want to update the email address of an employee with an employee_id of 5.

UPDATE employees
SET email = '[email protected]'
WHERE employee_id = 5;

Output:

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Update Multiple Rows

Now, suppose you need to give a raise to all employees in a particular department. The following query shows how you could do this:

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

Output:

Query OK, 10 rows affected (0.02 sec)
Rows matched: 10  Changed: 10  Warnings: 0

Updating Rows with Joins

In some scenarios, you might need to update rows in one table based on the values in another table. MySQL allows you to perform updates with joins. Here’s how:

UPDATE employees e
JOIN departments d ON e.department_id = d.department_id
SET e.location = 'New Office'
WHERE d.name = 'Research and Development';

Output:

Query OK, 5 rows affected (0.03 sec)
Rows matched: 5  Changed: 5  Warnings: 0

Advanced Updating Techniques

MySQL’s update capabilities are not limited to just direct assignments; it can handle more complex operations and conditions. Let’s explore some advanced examples.

Conditional Update with CASE

You can use the CASE statement within an UPDATE to apply different calculations or set different values under certain conditions:

UPDATE employees
SET bonus = CASE
    WHEN performance = 'Outstanding' THEN 1000
    WHEN performance = 'Good' THEN 500
    ELSE 0
END WHERE year = 2023;

Output:

Query OK, 20 rows affected (0.04 sec)
Rows matched: 20  Changed: 20  Warnings: 0

LIMIT and ORDER BY in Updates

LIMIT and ORDER BY clauses can be particularly useful when you need to control the rows that are affected by your UPDATE statement, such as bulk updates or updates that need to be performed in chunks:

UPDATE employees
SET status = 'Inactive'
WHERE leave_date < CURDATE()
ORDER BY leave_date DESC
LIMIT 10;

Output:

Query OK, 10 rows affected (0.01 sec)
Rows matched: 10  Changed: 10  Warnings: 0

Safeguarding against Accidental Updates

MySQL 8 adds additional safety mechanisms to prevent accidental updates or deletions of data. For instance, setting the sql_safe_updates option to 1 will restrict statements that might inadvertently modify large numbers of rows without a WHERE clause or with a WHERE clause that MySQL considers risky:

SET sql_safe_updates = 1;

After setting this option, attempting to execute an update without a proper WHERE clause shall result in an error:

UPDATE employees
SET email = '[email protected]';

ERROR 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

Conclusion

This tutorial has walked you through several examples of how to update rows in MySQL 8 from the most basic to more advanced cases. You have seen how to perform simple updates, handle multi-row updates, use joins, leverage conditional logic with the CASE statement, and ensure safe updates with built-in MySQL features. Mastery of these techniques will greatly enhance your ability to manage and maintain your MySQL databases.