Introduction to MySQL UPDATE Statement

Most modern websites and applications ground on data collection, storage, and analysis. Databases take an active part in building the entire Web environment. That’s why it is crucial to ensure correct data retrieval from databases and appropriate ways of data manipulation. To modify your data properly, you will need to execute SQL queries. The current article will focus on the MySQL UPDATE statement and its use.

The essence and role of MySQL UPDATE statement 

Very often, we need to update a field in MySQL and alter the data stored in tables. We can do it using the UPDATE statement. This command can modify any field in a table. It applies both to updating a single row and multiple rows.

The MySQL UPDATE statement is among the most commonly used commands in MySQL. Let’s have a look at its syntax.

MySQL UPDATE syntax 

Dealing with the UPDATE query in MySQL, we most often have three particular scenarios to follow. They are: updating a MySQL table, updating a table with the data from another MySQL table, and updating multiple tables.

The simple shortened syntax template for the MySQL update row in a table is as follows:

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
[WHERE conditions];

The full syntax template for the same case is the following:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
 
value:
    {expr | DEFAULT}
 
assignment:
    col_name = value
 
assignment_list:
    assignment [, assignment] ...

A specific case of applying the MySQL UPDATE statement is updating the data in one table with the data from another MySQL table:

UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
[WHERE conditions];

The UPDATE statement works excellently for multiple tables. In such a case, the syntax template will get modifications, as follows:

UPDATE table1, table2, ... 
SET column1 = expression1,
    column2 = expression2,
    ...
WHERE table1.column = table2.column
AND conditions;

You always need to define the name of the table that is subject to updating.

The following elements are essential for the MySQL update syntax:

• The SET clause defines the columns subject to updating. After the MySQL update keyword, you must provide the names of the necessary columns and the values they must receive. If you update multiple columns, separate their names and new values with a comma.

Note: If the value you set is the same as the one the column has at the moment, the MySQL UPDATE command will detect this and won’t update it.

• The WHERE clause specifies the conditions of updating the rows, i.e., which values must be updated. Without this clause, the UPDATE statement applies to all rows in the column specified. In the case of updating multiple tables, the UPDATE statement applies to all rows in each table specified in table_references that satisfy the conditions. However, it will update every matching row once, even if it matches the conditions multiple times.
• The ORDER BY clause is optional. If you set it, it will make the UPDATE statement apply to rows according to the order specified in that clause.
• The LIMIT clause is optional. It limits the number of rows to be updated.

The UPDATE statement may include two optional modifiers, LOW_PRIORITY and IGNORE:

• LOW_PRIORITY can delay the MySQL UPDATE query execution. For instance, you may set UPDATE to take place only after all SELECT queries are executed. This modifier is suitable for those tables that use table-level locking (Memory, MyISAM, and MERGE).
• IGNORE lets the UPDATE statement continue the process even if errors do occur. It will exclude certain elements from the updating process instead. For instance, these are the rows with the duplicate-key conflicts occurring on unique key values – they won’t get updated. Also, if new values cause data conversion errors, those values are updated to the closest valid values. The IGNORE modifier is especially helpful when you need to update multiple rows.

MySQL UPDATE statement examples

The UPDATE query in MySQL is useful in many cases. Let’s consider some examples of applying this statement to the standard tasks in MySQL.

Using the UPDATE command to update a MySQL column

A simple basic MySQL UPDATE table example will illustrate how to change a value in MySQL. Let’s assume that we need to alter the student’s last name in a database. The query will be the following:

UPDATE students
SET last_name = 'Williams'
WHERE student_id = 384;

By executing this code, we alter the Students table. We change the last_name to Williams and specify the condition – the change applies to the student_id that is 384.

Applying MySQL UPDATE command to alter multiple columns

The MySQL update multiple columns task is also pretty common. In the following case, we want to change the city value for a group of students. Have a look at the following query:

UPDATE students
SET city = 'Birmingham',
    student_rep = 15
WHERE student_id > 20;

Here, we’ll get the MySQL update data in a table for a group of students. The command will update their city of origin to Birmingham, and the student_rep will be changed to 15. The change will apply to those students whose student_id is higher than 20. This way, the MySQL query to update column values for many columns is performed with one statement.

Updating MySQL data in a table with data from another table

In this case, we want to update the Students table with the data from the Teachers table. The appropriate UPDATE MySQL example is as follows:

UPDATE students
SET teacher = (SELECT teacher_id
            FROM teachers
            WHERE teachers.city_name = students.city_name)
WHERE student_id < 20;

In this example, if the teacher’s home city from the Teachers table matches the student’s home city from the Students table, that teacher gets assigned to those students. The teacher_id is copied to the teacher field of the Students table. The change will apply to those students whose student_id is lower than 20.

Editing MySQL values in multiple tables at once

Here we want to update more than one table with a single UPDATE command.

UPDATE students, teachers
SET students.city = teachers.city
WHERE students.student_id = teachers.teacher_id;

This MySQL UPDATE command updates the city field in the Students table to the city from the Teachers table. The condition that determines the update is to have the student_id matching the teacher_id.

This way, we’ve explored the most common scenarios of using the UPDATE query to update a field in MySQL. However, this task becomes much more straightforward with appropriate tools.

Among the many software solutions for MySQL tasks, it’s worth paying particular attention to the popular dbForge product line of specialized solutions for different databases.

Executing MySQL UPDATE command with dbForge Studio for MySQL

For MySQL operations, all the necessary options are present in dbForge Studio for MySQL. This multi-featured software allows you to perform all operations with MySQL databases thanks to its impressive functional capabilities related to MySQL database development, management, and administration.

Let’s see how the dbForge Studio for MySQL copes with MySQL UPDATE use cases.

One of the primary features of the Studio is writing, executing, and optimizing the performance of SQL queries. The auto-completing and formatting options let the users eliminate possible typos and accelerate coding. An automated SQL syntax checker helps to spot and correct errors on the fly.

In our test case, we have the following Customers table. It contains the customers’ information, including their first and last names and email addresses.

Assuming that the data in the table has changed over time, we need to update some of the column values. For instance, Mary Smith’s email must become [email protected]. And let’s say we also have to change Barbara’s last name to Johnson due to her recent marriage.

We will have to execute two MySQL UPDATE statements as the changes relate to different rows of the table.

As you remember, we need to specify the table name after the UPDATE keyword. In our case, it is customers. The columns to be updated are specified in the SET clause, which in our case are email and last_name correspondingly.

However, it is no longer necessary to keep these things in mind: dbForge Studio for MySQL generates the UPDATE statement automatically. There is no need to type the UPDATE query in MySQL manually.

Thus, you will need to do the following:

1. Select the necessary table in Database explorer and click Retrieve Data. Another way is to enable the Data Grid mode to view the table data.
2. Pick the column values you need to update. Note: You can select a single value or multiple values by holding the Ctrl key.
3. Right-click the value (multiple values) and select Generate Script As from the menu. 
4. Go to Update > To SQL Document.

The new SQL document page opens. It already has all the necessary syntax provided for the MySQL UPDATE command.

You can replace the outdated values and add the latest data as follows:

Having executed the query, you can check its status in the bottom right corner.

To make sure the query has been executed successfully with the values updated, return to the Data grid mode and click the Refresh icon.

As you see, the email and last_name values have been successfully updated.

Conclusion 

MySQL UPDATE query is so common and handy that you can’t avoid dealing with it when working with MySQL databases. It is equally efficient when you change a value in MySQL in a single field, in the entire table, or even several tables.

The use is simple but can turn out pretty time-consuming. Hence, handy professional tools like dbForge Studio for MySQL make all tasks faster, smoother, and much easier. We hope that the current article was of help to you.