MySQL UPDATE Statement

MySQL is a popular relational database management system used in applications ranging from small projects to large enterprises. The UPDATE statement in MySQL is essential for modifying existing data in a table. It’s commonly used to correct errors, update values, and make other necessary changes.

This article explores the structure and use cases of the UPDATE statement, with clear and concise real-life examples.

The UPDATE statement in MySQL is used to modify existing records or data in a table. It is commonly used to correct any errors previously made or update the values of a column. It’s important to remember that changes made through the UPDATE statement are permanent and cannot be undone.

To write an UPDATE statement, two keywords play a crucial role i.e. UPDATE and SET. With the help of the SET clause, we assign a new value to our columns.

Syntax:

UPDATE table_name
SET column_name = value
WHERE (condition);

Note: To be careful when updating records, always use the WHERE keyword with an UPDATE statement. The WHERE clause specifies the records where we want to make changes. Without the WHERE clause, all records of the table will be updated!

Create Table:

CREATE TABLE w3wiki(
  id varchar(100) PRIMARY KEY,
  name varchar(100),
  monthly_score int,
  contest_rank int,
  courses_enrolled int
);

INSERT INTO w3wiki(id, name, monthly_score, contest_rank, courses_enrolled)
VALUES('vish3001', 'Vishu', 50, 1, 10);

INSERT INTO w3wiki(id, name, monthly_score, contest_rank, courses_enrolled)
VALUES('Aayush05', 'Aayush', 48, 2, 8);

INSERT INTO w3wiki(id, name, monthly_score, contest_rank, courses_enrolled)
VALUES('Neeraj50', 'Neeraj', 46, 3, 6);

INSERT INTO w3wiki(id, name, monthly_score, contest_rank, courses_enrolled)
VALUES('Sumit65', 'Sumit', 45, 4, 5);

INSERT INTO w3wiki(id, name, monthly_score, contest_rank, courses_enrolled)
VALUES('Harsh45', 'Harsh', 42, 5, 7);

Output:

Let’s look at some examples of UPDATE statements in MySQL. These examples will help you understand the workings of the UPDATE statement and the different use cases of the statement.

Example 1: UPDATE Statement with Single Column

In this example, we will update single-column data for a particular row. We will update contest_rank to 10 for the id =”Neeraj50″.

Query:

UPDATE w3wiki
SET contest_rank = 10
WHERE id = 'Neeraj50';

SELECT * FROM w3wiki;

Output:

Explanation: The above image shows that the rank has been updated to 10 for the row where the id = “Neeraj50“. Previously it was 10 for this particular record. Thus we can conclude that our desired row data has been updated without hampering the other rows.

Example 2: Update With Multiple Columns

In this example, we will update two column values at once. We will update contest_rank and courses_enrolled for the id = “Sumit65”.

Query:

UPDATE w3wiki
SET contest_rank = 55, courses_enrolled = 15
WHERE id = 'sumit65';

SELECT * FROM w3wiki;

Output:

Explanation: In the above output, we can observe that the contest_rank and courses_enrolled are 55 and 15 respectively for the row with id = “Sumit65”. Previously contest_rank and courses_enrolled are 4 and 5 respectively for this particular record.

Example 3: UPDATE With String Values

In this example, we will update the name column for a particular field. We will update name = “Vishu Vaishnav” where id = “vish3001”.

Query:

UPDATE w3wiki
SET name = 'Vishu Vaishnav'
WHERE id = 'vish3001';

SELECT * FROM w3wiki;

Output:

Explanation: We can notice that the name has been changed to “Vishu Vaishnav” from “Vishu” for the field, where id is “vish3001”. Thus we can conclude that our data has been updated.

Example 4: UPDATE Without WHERE Clause

In this example, we will not use an update statement. We will increment the monthly score by 1 for each record. With the help of the below query, we can easily perform this task.

Query:

UPDATE w3wiki
SET monthly_score = monthly_score + 1;

SELECT * FROM w3wiki;

Output:

Explanation: We can notice the change in the monthly score. We can observe that the monthly score is incremented by 1 for each record.

Example 5: UPDATE With Complex Query

In this example, we are going to execute a complex query. We will increment contest rank by 5 and courses enrolled by 10 where the id is “vish3001” or the name is ‘Harsh’. To perform this task, we can simply execute the query shown below.

Query:

UPDATE w3wiki
SET contest_rank = contest_rank + 5, courses_enrolled = courses_enrolled + 10
WHERE id = 'vish3001' OR name = 'Harsh';

SELECT * FROM w3wiki;

Output:

Explanation: We can observe that the contest rank is incremented by 5 and the courses enrolled are incremented by 10 for both the field where the name is Neeraj and the second one where the id is “vish3001“.

IGNORE clause is used with the UPDATE statement in some database systems like MySQL. It allows users to control error handling during the update process.

Working of IGNORE clause on UPDATE Statement:

• Rows that violate unique key constraints (duplicate values) are skipped and not updated.
• Rows with data type conversion issues might be updated with the closest valid value instead of raising an error.

IGNORE Clause Syntax

UPDATE IGNORE table_name
SET column1 = 'new value'
WHERE column2 = 'condition';

The MySQL UPDATE statement is a powerful tool for modifying existing data in your database. It is essential for making corrections, updating values, and managing data effectively. However, it is crucial to use the WHERE clause to ensure that only the intended records are updated. This article provides you with an overview of the UPDATE statement, including syntax, examples, and best practices. With this knowledge, you can confidently use the UPDATE statement to manage your database records efficiently and accurately.

How do I update multiple columns in a MySQL table?

You can update multiple columns by separating each column-value pair with a comma in the SET clause. For example:

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

What happens if I omit the WHERE clause in an UPDATE statement?

Omitting the WHERE clause will update all rows in the table. Always include a WHERE clause to specify which rows to update and avoid accidental changes to the entire table.

What is the UPDATE the statement used for in MySQL?

The UPDATE statement is used to modify existing records in a table. It allows you to change one or more column values in one or more rows.