MySQL UPDATE Statement with Examples

The UPDATE statement in MySQL allows you to modify or update existing records in a database table. You can use it to update one or more column values in one record or multiple records that already exist in the table.

The UPDATE statement is one of the most commonly used SQL commands because data often changes over time. For example, you can use the UPDATE statement to:

• Change a student’s address.
• Update an employee’s salary.
• Correct a customer’s phone number.
• Modify product prices.
• Change order status.

Unlike the INSERT statement, which adds new records in the table, the UPDATE statement modifies records that already exist in the database table.

Syntax of UPDATE Statement in MySQL

The general syntax for using the UPDATE statement in a MySQL table is:

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

In the above syntax:

• UPDATE table_name specifies the name of the table whose records you want to modify.
• SET assigns new values to one or more columns.
• WHERE specifies which record(s) should be updated.
• If you omit the WHERE clause, MySQL updates all rows in the table.

Creating a Table in MySQL

Let’s create a table in MySQL to update records using the UPDATE statement.

CREATE TABLE students(
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

Insert Records into the Table

INSERT INTO students
VALUES
(101,'John',20,'New York'),
(102,'David',22,'Chicago'),
(103,'Emma',21,'Boston'),
(104,'Sophia',23,'Dallas');

Original Table

Examples of MySQL UPDATE Statement

Example 1: Update a Single Column

Let’s update a single column using the MySQL UPDATE statement in the existing table named students.

UPDATE students
SET city = 'Los Angeles'
WHERE student_id = 101;

Output:

In this example, MySQL finds the row with student_id = 101 and updates its city value. The values in all other columns remain unchanged.

Example 2: Update Multiple Columns

UPDATE students
SET age = 24,
    city = 'Houston'
WHERE student_id = 102;

In this example, the WHERE clause finds the row with student_id = 102. MySQL updates the values of the age and city columns. It updates the value of the age column from 22 to 24 and the value of the city column from Chicago to Houston. The values in all other columns remain unchanged. Thus, you can update multiple columns in an existing table using an UPDATE statement in MySQL.

Output:

Example 3: Update Multiple Rows

The following UPDATE statement changes the city to Mumbai for all students whose age is greater than 21 in the existing table.

UPDATE students
SET city = 'Mumbai'
WHERE age > 21;

Output:

In this example:

• MySQL examines each row in the existing table named students.
• The WHERE clause evaluates the condition age > 21 and selects all rows where the age is greater than 21.
• As you can see in this table, the rows David (22) and Sophia (23) satisfy the condition.
• MySQL updates the city column of these rows to Mumbai.
• The rows for John and Emma remain unchanged because their ages are not greater than 21.

This example demonstrates how a single UPDATE statement can modify multiple rows at once.

Example 4: Update All Records in the Existing Table

The following UPDATE statement changes the city of every student to Dhanbad in the existing table named students.

UPDATE students
SET city = 'Dhanbad';

Output:

In this example:

• We have not used the WHERE clause in the above query.
• Without a WHERE clause in the UPDATE statement, MySQL evaluates every row in the table.
• MySQL updates the value of the city column to Dhanbad for all rows.
• The values in the student_id, student_name, and age columns remain unchanged. As a result, every student now has Dhanbad as their city.

If you omit the WHERE clause in an UPDATE statement, MySQL updates all rows in the table. This is one of the most common mistakes made by beginners.

Example 6: MySQL UPDATE Statement with AND Operator

The following UPDATE statement with an AND operator changes the city to Pune for the student whose age is 22 and whose name is David.

UPDATE students
SET city = 'Pune'
WHERE age = 22
AND student_name = 'David';

Output:

In this example:

• We have used the AND operator in the MySQL UPDATE statement.
• The AND operator combines two conditions:
  • age = 22
  • student_name = ‘David’
• The AND operator returns TRUE only when all specified conditions are true. If even one condition is false, MySQL does not update that row.
• MySQL evaluates both conditions for each row in the table. It selects only that row which satisfies both conditions. In simple words, a row is selected only if both conditions are true.
• Since only the row for David satisfies both conditions, MySQL updates the city column of that row to Pune.
• The values in all other columns remain unchanged.

How Does AND Work?

Example 7: UPDATE Statement with OR Operator

The following MySQL UPDATE statement changes the city to Kolkata for students whose student_id is 101 or 103.

UPDATE students
SET city = 'Kolkata'
WHERE student_id = 101
OR student_id = 103;

Output:

In this example:

• The OR operator combines two conditions:
  • student_id = 101
  • student_id = 103
• The OR operator returns TRUE if at least one of the specified conditions is true.
• MySQL evaluates these conditions for each row in the table and selects it if at least one condition is true.
• Since the rows for John (student_id = 101) and Emma (student_id = 103) satisfy the condition, MySQL updates the city column of these rows to Kolkata.
• The rows for David and Sophia remain unchanged because they do not satisfy either condition.

How does OR work?

Example 8: MySQL UPDATE Statement with BETWEEN Operator

The following MySQL UPDATE statement changes the city to Bangalore for students whose age is between 20 and 22 in the existing database table.

UPDATE students
SET city = 'Bangalore'
WHERE age BETWEEN 20 AND 22;

How Does the BETWEEN Operator Work?

The condition “age BETWEEN 20 AND 22” is equivalent to “age >= 20 AND age <= 22.” This operator includes both boundary values (20 and 22).

Evaluation of Each Row

Since John, David, and Emma satisfy the condition, MySQL updates their city values.

Output:

In this example:

• MySQL checks every row in the table and evaluates the condition of age BETWEEN 20 AND 22.
• The BETWEEN operator selects values within a specified range, including the starting and ending values.
• MySQL selects those students in the existing table whose ages fall within the specified range.
• If the ages fall within the specified range, MySQL updates the city column of the selected rows to Bangalore.
• Sophia’s row remains unchanged because her age (23) falls outside the specified range.

Example 9: MySQL UPDATE Statement with IN Operator

The following MySQL UPDATE statement changes the city to Hyderabad for students whose IDs are 101, 103, or 104 in the existing database table.

UPDATE students
SET city = 'Hyderabad'
WHERE student_id IN (101, 103, 104);

How Does IN Operator Work?

The IN operator provides a shorter and cleaner way to check multiple values. The condition student_id IN (101, 103, 104) is equivalent to:

student_id = 101
OR student_id = 103
OR student_id = 104

Evaluation of Each Row

Since student IDs 101, 103, and 104 satisfy the condition, MySQL updates their city values.

Output:

In this example:

• MySQL checks whether the value of student_id exists in the list (101, 103, 104).
• The IN operator checks whether a value matches any value in a specified list. It provides a convenient alternative to multiple OR conditions.
• If a match exists in the table, MySQL selects that row in the table.
• MySQL updates the city column of all matching rows to Hyderabad in the existing table.
• David’s row remains unchanged because student_id = 102 is not included in the list.

UPDATE Statement with MySQL Functions

MySQL allows you to use built-in functions within an UPDATE statement. These functions can modify existing data while updating records in a table.

In the following example, the UPPER() function converts all student names to uppercase letters.

UPDATE students
SET student_name = UPPER(student_name);

How Does the UPPER() Function Work?

The UPPER() function converts all lowercase letters in a string to uppercase letters. When you use it in an UPDATE statement, the UPPER() function modifies the existing values and stores the updated values back into the table.

Examples:

Output:

In this example:

• The UPDATE statement does not contain a WHERE clause. Therefore, MySQL evaluates every row in the students table.
• For each row, MySQL applies the UPPER() function to the value stored in the student_name column.
• The UPPER() function converts each name to uppercase.
• MySQL stores the converted values back into the student_name column.
• The values in the student_id, age, and city columns remain unchanged.

Note that if you want to update only specific rows, use a WHERE clause. Look at the following MySQL query.

UPDATE students
SET student_name = UPPER(student_name)
WHERE student_id = 101;

This query converts only John to JOHN and leaves all other rows unchanged. Similarly, you can also use other built-in functions, such as UPPER(), LOWER(), CONCAT(), NOW(), and ROUND() with UPDATE statement.

Conclusion

The MySQL UPDATE statement is a powerful SQL command that modifies existing records inside a database table. With using this function, you can update a single row, multiple rows, one column, or several columns in the table.

By combining the UPDATE command with WHERE, AND, OR, IN, BETWEEN, functions, and expressions, you can efficiently manage database records in the existing table.

You should always verify your conditions before executing UPDATE queries because an incorrect query can modify a large amount of data.