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 = '[email protected]'
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,'@javatpoint.com','@tutorialandexample.com')
WHERE course_name = 'Testing';It will give the following output:
Leave a Reply