MySQL Update Statement

MySQL Update Statement for beginners and professionals with examples on CRUD, insert statement, select statement, update statement, delete statement, use database, keys, joins etc.

<< Back to MYSQL

next → ← prev

MySQL UPDATE Query

MySQL UPDATE query is a DML statement used to modify the data of the MySQL table within the database. In a real-life scenario, records are changed over a period of time. So, we need to make changes in the values of the tables also. To do so, it is required to use the UPDATE query.

The UPDATE statement is used with the SET and WHERE clauses. The SET clause is used to change the values of the specified column. We can update single or multiple columns at a time.

Syntax

Following is a generic syntax of UPDATE command to modify data into the MySQL table:

UPDATE table_name   
SET column_name1 = new-value1, 
        column_name2=new-value2, ...  
[WHERE Clause]

Parameter Explanation

The description of parameters used in the syntax of the UPDATE statement is given below:

Parameter	Descriptions
table_name	It is the name of a table in which we want to perform updation.
column_name	It is the name of a column in which we want to perform updation with the new value using the SET clause. If there is a need to update multiple columns, separate the columns with a comma operator by specifying the value in each column.
WHERE Clause	It is optional. It is used to specify the row name in which we are going to perform updation. If we omit this clause, MySQL updates all rows.

Note:

This statement can update values in a single table at a time.
We can update single or multiple columns altogether with this statement.
Any condition can be specified by using the WHERE clause.
WHERE clause is very important because sometimes we want to update only a single row, and if we omit this clause, it accidentally updates all rows of the table.

The UPDATE command supports these modifiers in MySQL:

LOW_PRIORITY: This modifier instructs the statement to delay the UPDATE command's execution until no other clients reading from the table. It takes effects only for the storage engines that use only table-level locking.

IGNORE: This modifier allows the statement to do not abort the execution even if errors occurred. If it finds duplicate-key conflicts, the rows are not updated.

Therefore, the full syntax of UPDATE statement is given below:

UPDATE [LOW_PRIORITY] [IGNORE] table_name
    SET column_assignment_list
    [WHERE condition]

Example:

Let us understand the UPDATE statement with the help of various examples. Suppose we have a table "trainer" within the "testdb" database. We are going to update the data within the "trainer" table.

Update Single Column

This query will update the email id of Java course with the new id as follows:

UPDATE trainer  
SET email = 'mike@tutorialandexamples.com'  
WHERE course_name = 'Java';

After successful execution, we will verify the table using the below statement:

SELECT * FROM trainer;

In the output, we can see that our table is updated as per our conditions.

Update Multiple Columns

The UPDATE statement can also be used to update multiple columns by specifying a comma-separated list of columns. Suppose we have a table as below:

This statement explains will update the name and occupation whose id = 105 in the People table as follows:

UPDATE People
SET name = 'Mary', occupation = 'Content Writer'
WHERE id = 105;

We can verify the output below:

UPDATE Statement to Replace String

We can also use the UPDATE statement in MySQL to change the string name in the particular column. The following example updates the domain parts of emails of Android course:

UPDATE Trainer_table
SET email = REPLACE(email,'@TheDeveloperBlog.com','@tutorialandexample.com')
WHERE course_name = 'Testing';

It will give the following output:

Next TopicMySQL Delete Query

← prev next →

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com