MySQL UPDATE Statement

MySQL is a widely used relational database management system. It is used in both simple and large applications. In MYSQL, the UPDATE statement is used to make the desired changes in our table.

In this article, we will have a closer look at the structure of the UPDATE statement with its wide use cases. We will see its uses in real-life scenarios. We will be providing clear and concise examples of the UPDATE statement too.

UPDATE Statement

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 keep in mind 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!

Demo MySQL Database

We will use the following table for our examples.

Table – geeksforgeeks

To create this table on your system, write the following MySQL queries:

Create Table:

MySQL

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

Insert Values Into the Table:

MySQL

INSERT INTO geeksforgeeks(id,name,monthly_score,contest_rank,courses_enrolled)
VALUES('vish3001','Vishu',50,01,10);
INSERT INTO geeksforgeeks(id,name,monthly_score,contest_rank,courses_enrolled)
VALUES('Aayush05','Aayush',48,02,08);
INSERT INTO geeksforgeeks(id,name,monthly_score,contest_rank,courses_enrolled)
VALUES('Neeraj50','Neeraj',46,03,06);
INSERT INTO geeksforgeeks(id,name,monthly_score,contest_rank,courses_enrolled)
VALUES('Sumit65','Sumit',45,04,05);
INSERT INTO geeksforgeeks(id,name,monthly_score,contest_rank,courses_enrolled)
VALUES('Harsh45','Harsh',42,05,07);

MySQL UPDATE Statement Examples

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 a single column data for a particular row. We are going to update contest_rank to 10 for the id =”Neeraj50″.

Query:

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

SELECT * FROM geeksforgeeks;

Output:

Result – Single Column

In the above image, we can observe 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 are going to update contest_rank and courses_enrolled for the id = “Sumit65“.

Query:

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

SELECT * FROM geeksforgeeks;

Output:

Result – Multiple Columns

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 geeksforgeeks
SET name = 'Vishu Vaishnav'
WHERE id = 'vish3001';

SELECT * FROM geeksforgeeks;

Output:

Result – String Value Update

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 geeksforgeeks
SET monthly_score = monthly_score + 1;

SELECT * FROM geeksforgeeks;

Output:

Result – Increment By 1

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 geeksforgeeks
SET contest_rank = contest_rank + 5, courses_enrolled = courses_enrolled + 10
WHERE id = 'vish3001' OR name = 'Harsh';

SELECT * FROM geeksforgeeks;

Output:

Result – Complex Query

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

IGNORE Clause with UPDATE Statement

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';

Key Takeaways:

• It’s crucial for modifying existing data within a table, allowing precise updates to maintain and manage database content accurately.
• Implementing a WHERE clause is vital for targeting specific rows, ensuring updates are applied correctly without affecting unintended data.
• The ability to update multiple columns simultaneously enhances flexibility and efficiency in data management tasks.
• While powerful for batch updates, it necessitates careful use to prevent accidental data overwrites, underscoring the importance of precise query formulation.