MySQL MariaDB

MySQL Update Statement

MySQL is an open-source Database Management system, which can be used for both small and large projects. Developed by The Oracle Corporation, it uses standard SQL behind it. In database management, CRUD operations are a kind of basic requirement to know.

In this article, we will learn to update the data in MySQL tables using MySQL’s provided UPDATE statement. An UPDATE statement is basically a DML (Data Manipulation Language) statement because it modifies or updates the data.

Syntax

The syntax for updating a column or columns in a table in MySQL is:

UPDATE table_name
SET
column_name = value,
...
[WHERE condition]

In this syntax, table_name is the table in which you are willing to update any column.

By using the SET clause, we can assign new values to multiple columns by using the equal sign “=”.

column_name is that column where you want to make an update.

In the end, we can also provide the WHERE clause to apply some condition or filter the updating process.

Let’s make it clear by showing an example in which we would update the value of a column inside a table.

Example

First, open up your terminal and log in to the MySQL shell and choose the database in which you want to update a table. In order to see all the tables inside a database, run this command:

SHOW TABLES;

We have one table in our selected database. Let’s see whether some data exist in it or not. To see the data in a table, run the SELECT command as follows:

SELECT * FROM cars;

Suppose we want to update the car’s name and manufacturing date of the third row from “BMW I8” to “BMW M4” where car_id is 3, Then the query for updating the name and date would be like this:

UPDATE table_name
SET
car_name = ‘BMW M4’,
man_date = ‘2020-10-10’
WHERE car_id = 3;

After running the update command and having an output of “1 row(s) affected”, now, let’s view the table:

SELECT * FROM cars WHERE car_id = 3;

As you can see in the screenshot given below, the data of the third row is updated successfully.

So, this is how you can update the data of any table in MySQL using the UPDATE statement.

Conclusion

We have come to know the utilization of UPDATE statement and how we can update data in any MySQL database. Then, we have discussed how to update a single column, as well as multiple columns at once. Lastly, we have also seen the use of the WHERE clause.

About the author

Shehroz Azam

A Javascript Developer & Linux enthusiast with 4 years of industrial experience and proven know-how to combine creative and usability viewpoints resulting in world-class web applications. I have experience working with Vue, React & Node.js & currently working on article writing and video creation.