MySQL UPDATE – How to update values in a table?

By /
MySQL UPDATE statement

In this tutorial, we will learn about the MySQL UPDATE Statement. It may happen that you entered a wrong value in a column for a record in the table. Another situation may arise that a certain value may need to be changed over time. MySQL provides us with the UPDATE statement for such operations.

The MySQL UPDATE Statement is used to modify existing records in a table. The UPDATE statement is an example of Data Manipulation Language (DML).

Syntax of MySQL UPDATE

UPDATE table_name SET column1=value1, column2=value2,... columnN=valueN
WHERE condition;Code language: SQL (Structured Query Language) (sql)

It is important to note that if you do not mention the WHERE clause in the UPDATE statement, all records in the table will be updated with the given values.

Examples of MySQL UPDATE

Let us work on the Student table we worked on in the CREATE table and INSERT statement tutorials.

Students Table Update
Students Table

1. UPDATE Statement without WHERE Clause

In the syntax, I mentioned that if we omit the WHERE clause in the UPDATE statement, all the records in the table will be updated with a given value for that column. Let me demonstrate that first.

Let’s make the students happy (for a while) by giving all of them a 100 in Maths.

We do this using the query,

UPDATE Students SET Maths=100;Code language: SQL (Structured Query Language) (sql)

And after using the SELECT statement, we get the output of our updated table as,

Update Entire Column

As you can see, all students have been given 100 in Maths. So bear in mind to use a WHERE clause with the UPDATE statement unless you want all values to be updated.

2. Updating Multiple Columns using the WHERE clause

Suppose there was an error on part of the examiner while entering marks in English and Maths for a student named Devika Kulkarni. Her actual marks are 70 and 95 in English and Maths respectively and we know her ID to be 2. We update her record using the query,

UPDATE Students SET English=70, Maths=95 WHERE ID=2;Code language: SQL (Structured Query Language) (sql)

And after using the SELECT statement, we get the output of our updated table as,

Update Multiple Columns Example 2

3. Updating a Single Column using the WHERE clause

What if you realize there was another error while entering marks? This time it is for a student named Jay Bhatnagar. He scored 88 in Maths. We will use his name (instead of ID) to update his record using the query,

UPDATE Students SET Maths=88 WHERE Name='Jay Bhatnagar';Code language: SQL (Structured Query Language) (sql)

And after using the SELECT statement, we get the output of our updated table as,

Update Single Row Example 3

Conclusion

The UPDATE statement is an important and fundamental DML statement in MySQL. As you deal more and more with tables and databases, you will notice that having a command over the UPDATE statement is important. I highly recommend you to check the below references.

References