The update statement of MySQL

In MySQL, the existing table data can be modified by using the Update statement. The MySQL Update command is used with the Set keyword to modify data.

Depending on the requirement you can modify single field data, multiple columns data or multiple fields data in several rows by using a single Update – Set query in MySQL.

This tutorial will show you how to use the Update statement with MySQL tables.

MySQL Update – Set Syntax

Following is the general syntax of using the update query in MySQL:

In above syntax, you can see the Update keyword is followed by the name of a table where you want to update data. This is followed by the Set keyword and then comes the column names with new values. If you are updating more than one columns then separate each by a comma.

After that, you can see the Where clause is used to select records that should be updated. If you do not use Where clause, the whole table data of given columns will be updated. We will show you how later in this tutorial.

Let us go through examples now to use Update MySQL statement in our table, tbl_employee. If you did not see already, this is how our example table is created:

An Update example without Where clause

As mentioned earlier, you should use Where clause in the Update query, generally. However, if you do not use it, the records of all given columns with the new values will be updated as shown below:

The table data will look like this after executing this query:

From the above graphic, it is obvious that all records of the emp_salary column are updated by the given value in Update-Set query. Normally, you would not want it.

In order to update specific rows of data, we use theW here clause as explained in the following example.

An example of using Update – Set with where clause

In this example, only a specific row’s data is changed by using the Update statement with the where clause. The query will update the record of emp_id = 5. That means emp_salary for the emp_id=5 will be changed to 4000.

In that case, we changed Ben’s salary column by using Ben’s emp_id column value, as this is unique in the table.

An example with multiple conditions in update statement

In the following example, we will use two conditions in the Where clause and then update the matched record by using the Update command.

You can see, only Ben’s emp_salary column is updated.

Multi-column modification example by Update statement

In this example, we will modify two columns values by using the Update query of MySQL.

As you can see, both columns are updated with the new values by using Update command.

Further Reading: insert into command of MySQL | MySQL create table