Admin
Hi, Welcome to this tutorial, In this tutorial, you will learn everything about the MySQL Update statement with the help of Syntax and Examples. As we know, updating the table is one of the most important tasks for every developer and database administrator. that’s shy throughout this article we will explore MySQL UPDATE statement.
Headings of Contents
Introduction of MySQL UPDATE Statement
A MySQL UPDATE statement is a DML ( Data Manipulation Language ) statement that is used to update records in the database table. It allows us to update one record at a time or more than one record at a time. This is one of the most used SQL statements by most of developers and database administrators.
Syntax
This is the syntax of the MySQL UPDATE statement:
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] ...
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET assignment_list
[WHERE where_condition]
Explanation of the above syntax:
- LOW_PRIORITY is an optional parameter. It ensures that the update will be delayed until no other client reads data from the database table.LOW_PRIORITY may be used with MyISAM, MEMORY, and MERGE tables that used table-level locking.
- IGNORE is also an optional parameter that ensures that The UPDATE statement does not abort even if any errors are encountered during the update. The rows for which duplicate key conflict occurs on unique key values are not updated.
- Now, you will have to specify the name of the table that you want to update. The table name should be after the UPDATE statement.
- Now, you have to specify the name of the column name along with the new value which you want to update, In order to update multiple, you will have to specify all those columns along with values separated by a comma (,). This should be after the SET statement.
- Third, You have to specify a condition that is eligible for which record will update or not. it is an optional keyword, if you omit it, It will update all records in the table.
Remember:- WHERE clause is one of the most important statement in MySQL UPDATE statement. Because sometimes we want to update some specific records in the table, at that time where plays most important role.During the using of MySQL UPDATE statement you have to be very careful, If you omit it, it will modify all the records from the table.
Now, let’s see the MYSQL UPDATE table statement with the help of the proper examples.
MYSQL UPDATE Table Examples
I have created a table employee have some records and now I will try to update existing records from this table. As you can see employee table and its records.
| id | emp_id | first_name | last_name | company | |
|---|---|---|---|---|---|
| 1 | emp_1 | Vishvajit | Rao | [email protected] | XYZ Pvt Ltd |
| 2 | emp_2 | Vinay | Kumar | [email protected] | ABC Pvt Ltd |
| 3 | emp_3 | Minakshi | Kumari | [email protected] | NULL |
| 4 | emp_4 | John | Doe | [email protected] | NULL |
| 5 | emp_5 | Akash | Saini | [email protected] | NULL |
| 6 | emp_6 | Harshita | Singh | [email protected] | NULL |
| 7 | emp_7 | Aysh | Shukla | [email protected] | NULL |
| 8 | emp_8 | Abhishek | Mishra | [email protected] | NULL |
| 9 | emp_9 | Vaishnavi | Mehta | [email protected] | NULL |
| 10 | emp_10 | Mahima | Chaudhari | [email protected] | NULL |
Using the MYSQL UPDATE statement to update the value of the single column
In this example section, I am about to update the record of only a single employee whose emp_id is emp_10.
As you can see, Before updating the value of columns ( emp_id, first_name, last_name, email) of an employee whose emp_id is emp_10.
mysql> SELECT emp_id, first_name, last_name, age, email FROM employee WHERE emp_id = 10; +--------+------------+-----------+------+-----------------+ | emp_id | first_name | last_name | age | email | +--------+------------+-----------+------+-----------------+ | emp_10 | Mahima | Chaudhari | 27 | [email protected] | +--------+------------+-----------+------+-----------------+ 1 row in set (0.00 sec)
Now, I am going to update the value of the email column from ‘[email protected]‘ to ‘[email protected]‘.
The UPDATE statement will be:
UPDATE employee
SET
email = '[email protected]'
WHERE
emp_id = emp_10;
Once the above statement is executed successfully, the value of the employee will be successfully changed. You can verify by using MySQL SELECT statement.
mysql> SELECT emp_id, first_name, last_name, email FROM employee WHERE id = emp_10; +--------+------------+-----------+--------------------------------------+ | emp_id | first_name | last_name | email | +--------+------------+-----------+--------------------------------------+ | emp_10 | Mahima | Chaudhari | [email protected] | +--------+------------+-----------+--------------------------------------+ 1 row in set (0.00 sec)
So this is how you can use MySQL UPDATE statement to update the value of a single column in MySQL.
Using MySQL Update statement to update the multiple columns
MySQL Update also allows us to update multiple records at the same time. To update more than one record at the same time, we have to specify multiple columns along with their value and all those columns should be separated by a comma.
As you can see in the data, the value of the company column is NULL after the first two records, Now My requirement is two updates in the value of email and company. The value of the email should be a combination of first_name and last_name and the value of a company should be ‘Programming Funda‘.
Before updating the records:
mysql> SELECT id, first_name, last_name,email, company FROM employee;
+----+------------+-----------+---------------------------------------+-------------+
| id | first_name | last_name | email | company |
+----+------------+-----------+---------------------------------------+-------------+
| 1 | Vishvajit | Rao | [email protected] | XYZ Pvt Ltd |
| 2 | Vinay | Kumar | [email protected] | ABC Pvt Ltd |
| 3 | Minakshi | Kumari | [email protected] | NULL |
| 4 | John | Doe | [email protected] | NULL |
| 5 | Akash | Saini | [email protected] | NULL |
| 6 | Harshita | Singh | [email protected] | NULL |
| 7 | Ayush | Shukla | [email protected] | NULL |
| 8 | Abhishek | Mishra | [email protected] | NULL |
| 9 | Vaishanvi | Mehta | [email protected] | NULL |
| 10 | Mahima | Chaudhari | [email protected] | NULL |
+----+------------+-----------+---------------------------------------+-------------+
10 rows in set (0.00 sec)This will be the updated statement of the table.
UPDATE employee
SET
company = 'Programming Funda',
email = concat(lower(first_name),'.',lower(last_name), '@programmigfunda.com')
WHERE
id > 2;
After updating the records:
mysql> SELECT id, first_name, last_name,email, company FROM employee;
+----+------------+-----------+--------------------------------------+-------------------+
| id | first_name | last_name | email | company |
+----+------------+-----------+--------------------------------------+-------------------+
| 1 | Vishvajit | Rao | [email protected] | XYZ Pvt Ltd |
| 2 | Vinay | Kumar | [email protected] | ABC Pvt Ltd |
| 3 | Minakshi | Kumary | [email protected] | Programming Funda |
| 4 | John | Doe | [email protected] | Programming Funda |
| 5 | Akash | Saini | [email protected] | Programming Funda |
| 6 | Harshita | Singh | [email protected] | Programming Funda |
| 7 | Ayush | Shukla | [email protected] | Programming Funda |
| 8 | Abhishek | Mishra | [email protected] | Programming Funda |
| 9 | Vaishanvi | Mehta | [email protected] | Programming Funda |
| 10 | Mahima | Chaudhari | [email protected] | Programming Funda |
+----+------------+-----------+--------------------------------------+-------------------+
10 rows in set (0.00 sec)So, have you seen in the above update statement, how I am updating the value of the email and the value of the company column? Might be you are confused about the email update statement, wait I am about to explain that statement.
- Firstly I have converted the value of the first_name and last_name columns in lower case using the lower() function.
- Second I have used concat() function to concatenate first_name, last_name, and ‘@programmigfunda.com‘.
Using MySQL Update statement with MySQL REPLACE function
MySQL update statement could be used with the MySQL REPLACE function. MySQL REPLACE function is all about replacing the existing value with a new value within a column.
The REPLACE() function takes three parameters which are as follows.
- string:- It represents the original string.
- substring:- String to be replaced.
- string:- The new replacement string.
Note:- Remember, These three parameters would be required.
In the below employee table records, You can see there are two emails left to be updated with the ‘programmigfunda.com’ value. Now I am about to replace only ‘gmail.com‘ with ‘programmigfunda.com‘ using REPLACE() function.
Before Update the records:
mysql> SELECT id, first_name, last_name,email FROM employee; +----+------------+-----------+--------------------------------------+ | id | first_name | last_name | email | +----+------------+-----------+--------------------------------------+ | 1 | Vishvajit | Rao | [email protected] | | 2 | Vinay | Kumar | [email protected] | | 3 | Minakshi | Kumary | [email protected] | +----+------------+-----------+--------------------------------------+ 4 rows in set (0.00 sec)
Statement to replace ‘gmail.com’ with ‘programmingfunda.com’ using REPLACE() function.
UPDATE employee
SET
email = REPLACE(email,
'gmail.com',
'programmigfunda.com')
WHERE
id IN (1 , 2);
Employee table records after replacing:
mysql> SELECT id, first_name, last_name,email FROM employee; +----+------------+-----------+--------------------------------------+ | id | first_name | last_name | email | +----+------------+-----------+--------------------------------------+ | 1 | Vishvajit | Rao | [email protected] | | 2 | Vinay | Kumar | [email protected] | | 3 | Minakshi | Kumary | [email protected] | +----+------------+-----------+--------------------------------------+ 4 rows in set (0.00 sec)
So this is how we could use MySQL to replace the () function with the MYSQL UPDATE statement.
MySQL UPDATE Statement Reference:- Click Here
Related Articles:
Summary
So in this tutorial, we have learned about the MySQL UPDATE statement to update the existing records from the table. This is a most useful command in MySQL which is used by most developers and database administrators and as a developer we should have to have knowledge about this MySQL UPDATE statement.
One thing, I would like to recommend you, Always be careful during the use of update statements in real-life projects because if you will omit the WHERE clause in the MySQL UPDATE statement it would modify all the records in the table that’s why you always should use WHERE clause especially when you are going to work with this UPDATE statement.
If you found this article helpful, please share and keep visiting for further MySQL Tutorials.
Thanks for your valuable time….
