MySQL MariaDB

MySQL Update Row in Table

Updating values in a database is a common task especially on constantly changing data. For this tutorial, we shall look at the MySQL UPDATE statement that allows you to modify rows in a table.

Before we begin, we assume that you have installed a MySQL server on your system and can access a specific database. If you need a sample database to work with, consider the resource provided below:

https://dev.mysql.com/doc/index-other.html

In the resource above, you will get a zipped archive of the sample database you can use to test the concepts in this tutorial.

Basic Usage

The basic usage of the MySQL UPDATE statement is simple and you can use it to update the columns of the existing rows in a specified table with the set values.

The basic syntax of the UPDATE statement is:

UPDATE [LOW_PRIORITY] [IGNORE] table_name
    SET assignment_list
    [WHERE condition];

We start by calling the UPDATE statement followed by modifiers (continue reading to learn more) and the table name.

In the second statement, we set the column we wish to update and the values we need to change. You can update multiple columns by specifying the assignments in the form of an expression or comma-separated values.

To avoid updating all the rows in the entire table, we specify a condition restricting the UPDATE command. For example, this could be WHERE id=2, etc. We use the WHERE statement followed by the condition as shown in the third line.

You can also set an ORDER BY clause that forces MySQL to update the rows in the order specified.

Modifiers

MySQL UPDATE statement supports two modifiers—as seen in the above example. These are:

  1. LOW_PRIORITY: This modifier tells the UPDATE query to delay the operation until no connections are reading from the specified table.
  2. IGNORE: The IGNORE modifier allows the UPDATE query to continue updating even if an error occurs.

MySQL Update Use Cases

Let us now consider an illustration of updating values using the UPDATE command. We will start with a simple one where we update a single column.

Update Single Column

If you are using the Sakila sample database, consider the film table with sample information as shown below:

DESC film;
+----------------------+
| Field                |
+----------------------+
| film_id              |
| title                |
| description          |
| release_year         |
| language_id          |
| original_language_id |
| rental_duration      |
| rental_rate          |
| length               |
| replacement_cost     |
| rating               |
| special_features     |
| last_update          |
+----------------------+

If we query the data stored in that table as shown in the query below (the Sakila database contains lots of information; ensure to limit when querying some tables.)

SELECT * FROM sakila.film LIMIT 5;

You will get sample information as shown in the table below:

NOTE: It is good to have a visual reference of how your database is organized to ensure no errors occur or to perform invalid queries.

Now that we know what the database looks like, we can start to update a specific column. In this example, let us update the rating of the film ACADEMY DINOSAUR to a value “PG-13.”

Consider the query shown below:

UPDATE sakila.film
     SET rating=”PG-13
WHERE
     film_id=1;

Once the above query executes, the value of the ratings for the movie where id=1 is set to PG-13.

You can confirm using the query shown below:

mysql> SELECT rating FROM sakila.film LIMIT 3;

+--------+

| rating |

+--------+

| PG-13 |

| G |

| NC-17 |

+--------+

3 rows in set (0.00 sec)

Update Multiple Columns

Updating multiple columns is similar to updating a single command, but you specify multiple values in the SET clause as shown in the query below:

UPDATE sakila.film SET rating="PG-13",rental_rate=1.99 WHERE film_id = 2;

In this example, we are updating the second movie values to the rating of PG-13 and the rate of 1.99.

Confirm the changes have applied successfully:

mysql> SELECT rental_rate, rating FROM sakila.film LIMIT 2;

+-------------+--------+

| rental_rate | rating |

+-------------+--------+

| 0.99 | PG-13 |

| 1.99 | PG-13 | <------------

+-------------+--------+

2 rows in set (0.00 sec)

As you can see, you can add multiple columns by separating their values in commas.

Conclusion

In this tutorial, you learned how to use the MySQL UPDATE command to change values in a table column.

If you need more experience with MySQL, consider our tutorials provided below:

https://linuxhint.com/category/mysql-mariadb/

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list