SQL UPDATE Statement 

The SQL UPDATE query is used to modify existing records in a database table. It allows changing the values stored in one or more fields of selected records without replacing the entire record. Whether we need to resolve errors or update outdated information, the UPDATE statement provides an efficient way to update the records. In this blog, let us explore the UPDATE statement in SQL in detail.

Table of Contents:

• What is an UPDATE Statement?
• When to Use an UPDATE Statement in SQL?
• Basic Methods of Using the UPDATE Statement in MySQL
  • Method 1: Updating a Single Record in MySQL
  • Method 2: Updating Multiple Rows in MySQL
  • Method 3: Updating Multiple Columns in MySQL
  • Method 4: Updating with Subquery in MySQL
• Advanced Methods of Using the UPDATE Statement in MySQL
  • Method 1: Using UPDATE with INNER JOIN in MySQL
  • Method 2: Using UPDATE with LEFT JOIN in MySQL
  • Method 3: Using UPDATE with RIGHT JOIN in MySQL
  • Method 4: Using UPDATE with SELF JOIN in MySQL
• Common Mistakes and Solutions to Avoid Them While Performing UPDATE Queries in SQL
• Performance Comparison of Each Method
• Real-world Examples
• Best Practices
• Conclusion

Before getting started with the UPDATE Statement in SQL, let’s create a table called Intellipaat and insert some values into it.

CREATE TABLE Intellipaat (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    role VARCHAR(100),
    salary DECIMAL(10,2),
    department VARCHAR(50),
    status VARCHAR(20),
    last_login DATE
);
INSERT INTO Intellipaat (name, email, role, salary, department, status, last_login) VALUES
('Knight', '[email protected]', 'Content Writer', 50000, 'IT', 'Active', '2024-01-15'),
('Jane', '[email protected]', 'Manager', 75000, 'IT', 'Active', '2023-10-20'),
('Andrew', '[email protected]', 'Research Analyst', 40000, 'IT', 'Active', '2023-05-10'),
('Sanjay', '[email protected]', 'Content Writer', 55000, 'HR', 'Active', '2022-12-01'),
('Black', '[email protected]', 'Research Analyst', 42000, 'IT', 'Inactive', '2021-09-15');
Select * from Intellipaat;

This is how the Intellipaat table looks.

What is an UPDATE Statement in SQL?

In SQL, the UPDATE Statement is used to modify the existing records in the database based on a given condition. The SQL query allows us to change one or more rows based on a specific condition.

Syntax:

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

Example:

-- Let us update an Employee’s name 
UPDATE Intellipaat
SET name = 'John Mathew'
WHERE name = 'Jane';
-- To verify the update statement
Select * from Intellipaat;

Output:

 

Explanation: Here, the WHERE Clause extracts the records with name = ‘Jane’. Only those employees with the name Jane are replaced with John Mathew.

When to Use an UPDATE Statement in SQL?

The UPDATE Statement can be used in various scenarios without deleting or reinserting the records. Below are some of the scenarios where the UPDATE Statement can be used in SQL.

• Data Entry Mistakes: UPDATE can be easily used to fix the incorrect data that is entered.
• Changing multiple values: If there is a change in prices or stock levels, then the UPDATE Statement can be used on multiple statements to update.
• Handling Inactive Records: The status of the account or subscriptions can be updated if it expires.
• Data Cleaning: The UPDATE Statement can be used to modify multiple records at once.
• Correcting Foreign Key Relationships: Changing references when the related record has been updated.

Basic Methods of Using the UPDATE Statement in MySQL

The UPDATE Statement in SQL can be used in many ways to modify the existing record effectively. It can be used to update single or multiple columns, as well as with subqueries and JOINS.

Method 1: Updating a Single Record in MySQL

The UPDATE statement in SQL allows you to change records in a table that already exists. When updating a record, it updates a specific record or row based on the condition specified in the WHERE clause.

Syntax:

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

Example:

-- To update the salary of an employee
UPDATE employees
SET salary = 60000
WHERE name = 'Black';
-- To check the updated records
Select * from Intellipaat;

Output:

Explanation: Here, the WHERE Clause filters out the records with name = ‘Black’. Only the black records are filtered, and the salary is updated to 60,000.

Method 2: Updating Multiple Rows in MySQL

There are two types of updating multiple rows, which include updating multiple rows without CASE WHEN and with CASE WHEN in SQL. 

Updating Multiple Rows using CASE WHEN in MySQL

This method is useful when we need to update multiple rows with different values based on a specific condition using CASE WHEN.

Syntax:

UPDATE table_name
SET column_name = CASE  
    WHEN condition1 THEN value1  
    WHEN condition2 THEN value2  
    ELSE column_name  
END  
WHERE columnname IN (value1, ...);

Parameters:

• UPDATE table_name: Specifies the table that has to be updated 
• CASE WHEN: Declares conditions to assign different values to different rows 
• WHERE column_name IN(…): Ensures that only specific records are updated

Example:

-- To update the role of multiple employees
UPDATE Intellipaat
SET role = CASE  
    WHEN id = 1 THEN 'Lead Developer'  
    WHEN id = 3 THEN 'Senior Sales Executive'  
    ELSE role  
END  
WHERE id IN (1, 3);
-- To check the updated records
Select * from Intellipaat;

Output:

Explanation: Here, the CASE WHEN statement updates different roles based on their ID. An employee’s role with ID=1 is updated to ‘Lead Developer’, and an employee’s role with ID=3 is updated to ‘Senior Sales Executive’.

Updating Multiple Rows without using CASE WHEN in MySQL

This UPDATE Statement in SQL allows us to update multiple rows at once by applying the same value to all the matching records.

Syntax:

UPDATE table_name
SET column_name = new_value
WHERE columnname IN (value1...);

Example:

-- To update the status
UPDATE Intellipaat
SET status = 'Inactive'
WHERE id IN (2, 4, 5);
-- To check the updated records
Select * from Intellipaat;

Output:

Explanation: The UPDATE statement updates the status of employees with IDs 2,4, and 5 to INACTIVE.

Difference Between Updating Multiple Records with and Without CASE WHEN

Parameter	With CASE WHEN	Without CASE WHEN
Use Case	It can be used when different rows have to be updated with different values	It can be used when we need to update every record with the same value
Syntax	CASE…WHEN…THEN… ELSE…END	SET column=value WHERE IN
Flexibility	It allows us to set different values in a single query	It allows us to update a single value to match the records
Performance	Due to the conditional checks, it is slower	Since the same value is assigned to every row, it is faster

Method 3: Updating Multiple Columns in MySQL

This method can be used when we need to update multiple attributes of a record in a single query.

Syntax:

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

Example:

-- To update the role and salary of an employee
UPDATE Intellipaat
SET role = 'Team Lead', salary = 95000
WHERE name = 'Sanjay';
-- To check the updated records
Select * from Intellipaat;

Output:

Explanation: Here, the UPDATE statement updates both the role and salary of the employee named Sanjay to Team Lead and 95000, respectively.

Method 4: Updating with Subquery in MySQL

The UPDATE statement’s subquery enables us to update the column values according to information that has been retrieved from another table or within the table itself.

Syntax:

UPDATE table_name  
SET column_name = (SELECT aggregate_function(column) FROM another_table WHERE condition)  
WHERE condition;

Example:

-- To update the salary of the Research Analyst based on the highest salary
UPDATE Intellipaat  
SET salary = (SELECT max_salary FROM (SELECT MAX(salary) AS max_salary FROM Intellipaat) AS temp_table)  
WHERE role = 'Research Analyst';
-- To check the updated records
Select * from Intellipaat;

Output:

Explanation: Here, the Intellipaat table’s highest salary (75,000) for all Research Analysts is updated.

Advanced Methods of Using the UPDATE Statement in MySQL

The UPDATE statement can also be used with different types of JOINS. It allows us to modify records in one table based on the matching values from another table.

To better understand UPDATE with JOIN, let us create a table called Intellipaat_Departments and insert some values into it.

CREATE TABLE Intellipaat_Departments (
    department_name VARCHAR(100),
    budget INT
);
INSERT INTO Intellipaat_Departments (department_name, budget)
VALUES 
( 'IT', 500000),
('Sales', 300000),
( 'HR', 200000);
Select * from Intellipaat_Departments;

This is how the table looks once it is created and inserted with values.

Method 1: Using UPDATE with INNER JOIN in MySQL

Using UPDATE Statement with INNER JOIN enables updating of records in one table in response to matching records in another table. Only the rows for which the JOIN condition is true get updated.

Syntax:

UPDATE table1  
INNER JOIN table2 ON table1.column = table2.column  
SET table1.column1 = value1  
WHERE condition;

Example:

UPDATE Intellipaat AS i  
INNER JOIN Intellipaat_Departments AS d ON i.department = d.department_name  
SET i.salary = d.budget / 10  
WHERE d.department_name = 'IT';
Select * from Intellipaat;

Output:

Explanation: Here, the INNER JOIN is based on the department column between the Intellipaat and Intellipaat_Departments tables. The rows from Intellipaat_Departments with department_name = ‘IT’ are filtered, and then only the salary of employees in the IT department is updated.

Method 2: Using UPDATE with LEFT JOIN in MySQL 

In an UPDATE Statement, the LEFT JOIN maintains all records intact while updating records from the primary table, even if the secondary table contains no matching records. 

Syntax:

UPDATE main_table AS m  
LEFT JOIN secondary_table AS s  
ON m.common_column = s.common_column  
SET m.update_column = s.value 
WHERE condition;

Example:

UPDATE Intellipaat AS i  
LEFT JOIN Intellipaat_Departments AS d  
ON i.department = d.department_name  
SET i.salary = d.budget / 20  
WHERE d.department_name IS NOT NULL;
Select * from Intellipaat;

Output:

Explanation: Here, all Intellipaat records are updated according to matching departments in Intellipaat_Departments using LEFT JOIN. While non-matching department employees’ salaries remain the same, matching department employees’ salaries are updated to budget/20.

Method 3: Using UPDATE with RIGHT JOIN in MySQL 

In an UPDATE Statement, the RIGHT JOIN ensures that all records from the secondary table are included in the operation by changing records in the main table based on matching values from the secondary table.

Syntax:

UPDATE main_table AS m  
RIGHT JOIN secondary_table AS s  
ON m.common_column = s.common_column  
SET m.update_column = s.value  
WHERE condition;

Example:

UPDATE Intellipaat AS i  
RIGHT JOIN Intellipaat_Departments AS d  
ON i.department = d.department_name  
SET i.salary = d.budget / 30  
WHERE d.department_name IS NOT NULL;
Select * from Intellipaat;

Output:

Explanation: Here, the salary is modified in the Intellipaat table by setting it to budget / 30. 

Method 4: Using UPDATE with SELF JOIN in MySQL 

In an UPDATE Statement, the SELF JOIN joins a table with itself to update records. Usually, a related column is used to change values based on conditions in the same table.

Syntax:

UPDATE main_table AS m1  
JOIN main_table AS m2  
ON m1.common_column = m2.common_column  
SET m1.update_column = m2.value 
WHERE condition;

Example:

UPDATE Intellipaat AS i1  
JOIN Intellipaat AS i2  
ON i1.department = i2.department  
SET i1.salary = i2.salary * 1.05  
WHERE i1.status = 'Active' AND i2.role = 'Manager';
Select * from Intellipaat;

Output:

Explanation: Here, this query raises all current employees’ salaries by 5% if they work in the same department as a manager. Thus, Jane’s salary is updated since it satisfies both (status = ‘Active’ AND role = ‘Manager) conditions.

Common Mistakes and Solutions to Avoid Them While Performing UPDATE Queries in SQL

1. Ignoring the WHERE Clause and updating all the records unknowingly 

Mistake: The WHERE Clause is not used

UPDATE Intellipaat SET salary = 60000;

Solution: Always prefer using a WHERE clause

UPDATE Intellipaat SET salary = 60000 WHERE department = 'IT';

2. Mismatch in data type

Mistake: Attempting to put a string in a column that contains numbers

UPDATE Intellipaat SET salary = 'Sixty Thousand' WHERE id = 1;

Solution: Check that the assigned value matches the column data type

UPDATE Intellipaat SET salary = 60000 WHERE id = 1;

3. Avoiding transactions in bulk updates

Mistake: Several records are being updated without a Rollback option.

UPDATE Intellipaat SET salary = salary * 1.10 WHERE department = 'IT';

Solution: Always use transactions in bulk updates/inserts for safe execution.

BEGIN TRANSACTION;
UPDATE Intellipaat SET salary = salary * 1.10 WHERE department = 'IT';
COMMIT; 
ROLLBACK; 

4. Not verifying affected rows before executing the Query

Mistake: Directly updating the records 

UPDATE Intellipaat SET salary = 70000 WHERE role = 'CEO';

Solution: Before updating, look into the affected rows.

SELECT * FROM Intellipaat WHERE role = 'CEO';  
UPDATE Intellipaat SET salary = 70000 WHERE role = 'CEO';

Performance Comparison of Each Method

 

Method	Use Case	Pros	Cons
Updating a Single Record	When we need to modify a single row	Simple and efficient	It is a complex task if we need to update multiple rows
Updating Multiple Rows	When we need to modify multiple rows based on a condition	Easy process for updating multiple rows at once	If the WHERE Clause is missing, it leads to accidental updates
Updating Multiple Columns	When we need to change multiple values in a single row	Can be executed in a single query	If many columns are involved, then it is complex
Updating with Subquery	When we need to update records based on another table	Enables dynamic updates	Does not function well on large datasets
Updating with JOINS	When we need to update data based on related data from another table	Updated using related table values	If the indexes are not optimized, it leads to a performance decrease

Real-world Examples

1. HR System: A company wants to increase the employee’s salaries who have been promoted recently.

Example:

CREATE TABLE Employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    role VARCHAR(50),
    salary DECIMAL(10,2)
);
INSERT INTO Employees (name, role, salary) VALUES
('Anand', 'Developer', 60000),
('Babu', 'Manager', 75000),
('Mathew', 'Analyst', 50000);
-- To check before update
Select * from Employees;
UPDATE Employees 
SET salary = 80000 
WHERE name = 'Babu';
-- To check after the update
Select * from Employees;

Output:

Before UPDATE:

After UPDATE:

Explanation: Here, Babu’s salary has been updated from 75,000 to 80,000 using the UPDATE Statement.

2. E-Commerce System: An e-commerce site wants to update the price of Electronics items to the average price of all the products.

Example:

CREATE TABLE Products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
);
INSERT INTO Products (product_name, category, price) VALUES
('Laptop', 'Electronics', 70000),
('Smartphone', 'Electronics', 50000),
('TV', 'Electronics', 60000),
('Sofa', 'Furniture', 25000),
('Table', 'Furniture', 15000);
-- To check before update
Select * from Products;
UPDATE Products 
SET price = (SELECT AVG(price) FROM Products WHERE category = 'Electronics') 
WHERE category = 'Electronics';
-- To check after the update
Select * from Products;

Output:

Before Update

After Update

Explanation: Here, the subquery makes a temporary table (temp) that stores the average price by category. The JOIN operation connects the Product table to the temporary table by referencing the category. Lastly, the UPDATE statement is used to update the prices in the Product table by implementing the precomputed avg_price.

Best Practices

• Use WHERE Clause Carefully: To prevent accidentally updating every row, always include a WHERE clause.
• Prevent Subqueries: To avoid MySQL error 1093, use derived tables.
• Consider a small dataset: Before using UPDATE, run SELECT to confirm the impacted rows.
• Utilize Transactions for Safety: Use updates in BEGIN TRANSACTION and COMMIT/ROLLBACK for security against data loss. 
• Improve Performance: Indexed columns should be utilized in the WHERE clause during updates, as this causes the updates to process faster.

Conclusion

An important command for updating data that is already in your database’s record table while preserving data integrity is the SQL UPDATE statement. Any database’s tables can be effectively managed with the UPDATE statement, which also ensures smooth and effective data updating. In this blog, you have gained knowledge of different methods of updating the records in SQL.

Take your skills to the next level by enrolling in SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with SQL interview questions, which are prepared by industry experts.