How to Use MySQL UPDATE Query in SQL

If you are looking to modify data that already exists in your MySQL database, the UPDATE query is the way to go. It allows you to update one or more columns in one or more rows in a table. In this article, we will go over the various ways to use this query in SQL.

Syntax of the UPDATE Query

Before we dive into the various functions and capabilities of the UPDATE query, let’s review the syntax:

UPDATE table_name 
SET column1 = value1, column2 = value2, ... 
dWHERE some_column = some_value;

• table_name: the name of the table you want to update
• column1 = value1, column2 = value2, ...: the columns you want to update with their new values
• WHERE some_column = some_value: the condition under which you want to update the rows (optional, but recommended)

Note that you must specify the WHERE clause, otherwise all rows in the table will be updated to the new values.

Updating a Single Column

Let’s start with the simplest case, updating a single column in a table. Here’s an example:

UPDATE employees 
SET last_name = 'Doe' 
WHERE employee_id = 1;

This query updates the last_name column of the employees table to 'Doe' for the row where employee_id equals 1. If you want to update more than one row, simply use the WHERE clause to specify your conditions.

Updating Multiple Columns

You can also update multiple columns at once with the UPDATE query. Here’s an example:

sUPDATE employees 
SET last_name = 'Doe', first_name = 'John' 
WHERE employee_id = 1;

This query updates both the last_name and first_name columns of the employees table to ‘Doe’ and ‘John’, respectively, for the row where employee_id equals 1.

Updating Multiple Rows

If you want to update multiple rows at once, you can do so by specifying multiple conditions in the WHERE clause. Here’s an example:

UPDATE employees 
SET last_name = 'Doe' 
WHERE department = 'Sales';

This query updates the last_name column of the employees table to ‘Doe’ for all the rows where department equals ‘Sales’. You can also use other operators like > or < to specify a range of rows to update.

Updating with Subqueries

You can also use subqueries to update data in one table with information from another. Here’s an example:

UPDATE employees 
SET department = ( SELECT department_name FROM departments WHERE departments.department_id = employees.department_id ) 
WHERE employee_id = 1;

This query updates the department column of the employees table with the department_name from the departments table where departments.department_id equals employees.department_id for the row where employee_id equals 1.

Updating with Joins

Similarly, you can use joins to update data in one table with information from another. Here’s an example:

UPDATE employees 
JOIN departments ON departments.department_id = employees.department_id 
SET employees.department = departments.department_name 
WHERE employees.employee_id = 1;

This query updates the department column of the employee’s table with the department_name from the department’s table where departments.department_id equals employees.department_id for the row where employee_id equals 1.

Conclusion

The UPDATE query is a powerful tool for modifying data in your MySQL database. Whether you want to update a single column or multiple columns or even use subqueries and joins to update data, the syntax is simple. Just specify your conditions with the WHERE clause to ensure you update only the rows you want.

Frequently Asked Questions

📕 Related articles about MySQL

• MySQL Text Data: A Comprehensive Guide
• Understanding MySQL DROP DATABASE Statement
• Overview of MySQL Programs: An In-Depth Breakdown
• How to use Join – Cartesian Join & Self Join
• Loading Data into a Table – A Guide to Efficient and Robust Data Management
• The Power of MySQL Select Data: Uncovering Hidden Gems