TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

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

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.

MySQL UPDATE Query

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.

MySQL UPDATE Query

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:

MySQL UPDATE Query

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:

MySQL UPDATE Query

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:

MySQL UPDATE Query
Next TopicMySQL Delete Query




Related Links:


Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf