In the previous article, I gave an introduction to SQL using MySQL database management system. The article laid a foundation of SQL by briefly explaining how to create a database, create a table, insert data into a table and use select statement to explore the data. In this article, I will show you how to update data in a table in MySQL.
While working with relational databases, in most instances, you will counter situations that require you to make some changes in the data you are working with. Some of these changes may be as simple as renaming an attribute of a row. However, in some cases, you will be required to make several changes to the table. UPDATE statement of SQL provides a great way of making such changes.
The UPDATE statement modifies existing data in a table. You can use it to make changes in the value or values of a column or multiple columns in a table. The general syntax for UPDATE statement is as follows;
= ‘value’ WHERE
Note the following while writing the syntax;
- First and foremost, you must specify the name of the table that you want to update data immediately after the UPDATE keyword.
- Secondly, you must specify the name of the column you want to update and the new value using the SET clause.
- Finally, you need to specify the rows that you want to update using criteria (conditions) in the WHERE clause. If you don’t specify the row, all the rows in the table will be updated.
Let us look at some practical examples. I will use the database and table I created for the previous article.
1. Updating a single column
First, let us start by selecting the table JobDetails so that we see how the columns and rows look like before we update.
We now want to update Designation column by changing Paul’s designation from Accountant to Finance Officer. The syntax and the output will be as follows;
When we select the row with the name Paul, we will find that the change has already been effected successfully as follows;
You can see that Paul’s designation has changed to Finance Officer.
2. Updating multiple columns
In this example, we want to update two columns for the second row. We will make changes in the Department and Salary columns. Let us change Minneh’s department to Production and change her salary to 236,400.
Note that you can be able to make changes to as many columns as you want in your table. What you need to do is to specify the columns you want to update and set the value while separating them using a comma as shown above.
Let us confirm the change.
From the figure above, Minneh’s department has been changed to Production while her salary increased to 236,000.
3. Updating using Arithmetic Operations
You can also update data in a table in MySQL using an arithmetic operator e.g. addition operator (+), minus operator (-), etc. This can only be used when updating a numeric value. Let us change Paul’s salary by adding 150 to it as follows;
Let us verify the change.
The change has been effected and Paul’s salary now reads 95,150.
The Update statement in MySQL is very important while working with databases. In this article, you have learnt how to update data in a table in MySQL by working with a single column, multiple columns and how to use an arithmetic operator to update numeric values.