{"id":240,"date":"2020-11-26T15:39:53","date_gmt":"2020-11-26T15:39:53","guid":{"rendered":"https:\/\/mysqlcode.com\/?p=240"},"modified":"2022-08-21T19:57:28","modified_gmt":"2022-08-21T19:57:28","slug":"mysql-update","status":"publish","type":"post","link":"https:\/\/mysqlcode.com\/mysql-update\/","title":{"rendered":"MySQL UPDATE – How to update values in a table?"},"content":{"rendered":"\n

In this tutorial, we will learn about the MySQL UPDATE<\/code> 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<\/code> statement for such operations.<\/p>\n\n\n\n

The MySQL UPDATE<\/code> Statement is used to modify existing records in a table. The UPDATE<\/code> statement is an example of Data Manipulation Language (DML).<\/p>\n\n\n\n

\n\n\n\n

Syntax of MySQL UPDATE<\/h2>\n\n\n
UPDATE<\/span> table_name SET<\/span> column1=value1, column2=value2,... columnN=valueN\nWHERE<\/span> condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It is important to note that if you do not mention the WHERE clause<\/a> in the UPDATE statement, all records in the table will be updated with the given values.<\/em><\/strong><\/p>\n\n\n\n
\n\n\n\n
Examples of MySQL UPDATE<\/h2>\n\n\n\n
Let us work on the Student table we worked on in the CREATE table<\/a> and INSERT statement<\/a> tutorials.<\/p>\n\n\n\n
\"Students
Students Table<\/figcaption><\/figure><\/div>\n\n\n\n
1. UPDATE Statement without WHERE Clause<\/h3>\n\n\n\n
In the syntax, I mentioned that if we omit the WHERE<\/code> clause in the UPDATE<\/code> statement, all the records in the table will be updated with a given value for that column. Let me demonstrate that first. <\/p>\n\n\n\n
Let’s make the students happy (for a while) by giving all of them a 100 in Maths<\/em>. <\/p>\n\n\n\n
We do this using the query,<\/p>\n\n\n
UPDATE<\/span> Students SET<\/span> Maths=100<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Update<\/figure><\/div>\n\n\n\n
As you can see, all students have been given 100 in Maths. So bear in mind to use a WHERE<\/code> clause with the UPDATE<\/code> statement unless you want all values to be updated.<\/p>\n\n\n\n
2. Updating Multiple Columns using the WHERE clause<\/h3>\n\n\n\n
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,<\/p>\n\n\n
UPDATE<\/span> Students SET<\/span> English=70<\/span>, Maths=95<\/span> WHERE<\/span> ID<\/span>=2<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
And after using the SELECT<\/code> statement, we get the output of our updated table as,<\/p>\n\n\n\n
\"Update<\/figure><\/div>\n\n\n\n
3. Updating a Single Column using the WHERE clause<\/h3>\n\n\n\n
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,<\/p>\n\n\n
UPDATE<\/span> Students SET<\/span> Maths=88<\/span> WHERE<\/span> Name<\/span>='Jay Bhatnagar'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
And after using the SELECT<\/code> statement, we get the output of our updated table as,<\/p>\n\n\n\n
\"Update<\/figure><\/div>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
The UPDATE<\/code> 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<\/code> statement is important. I highly recommend you to check the below references.<\/p>\n\n\n\n
References<\/h2>\n\n\n\n
And after using the SELECT<\/code> statement<\/a>, we get the output of our updated table as,<\/p>\n\n\n\n