How to Perform Batch Updates in SQL Server

The Update statement is a SQL keyword to update data in the database. We can update all the rows in the database or some values with the help of conditions. The update is a SQL keyword, and it falls under Data Manipulation Language (DML), as the name suggests it is used to manipulate the data.

Updating a single row at a time is a lengthy and time-consuming process. So, we have another term known as “Batch Update“, in which we can update multiple rows in a single batch at a time. It reduces the time to update multiple records.

How to Perform Batch Updates in SQL Server

To perform batch updates in SQL Server, use the UPDATE statement with a WHERE clause to specify the conditions for the batch, updating multiple rows at once.

Syntax of Update:

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

• Update: It is used to update the table,
• Set: It sets the new value to the table.

Steps to do Batch Update

Step 1: Create a database named EmployeeDetails.

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50),
LastName VARCHAR(50), City VARCHAR(50), Department VARCHAR(50), Salary DECIMAL(10, 2));

Step 2: Create a table Name Employee having EmployeelD, FirstName, LastName, City, Department, Salary.

Step 3: Add 15 records to the table.

INSERT INTO Employees (EmployeeID, FirstName, LastName, City, Department, Salary) VALUES
(1, 'Aarav', 'Sharma', 'Mumbai', 'IT', 50000.00),
(2, 'Aaradhya', 'Patel', 'Delhi', 'HR', 55000.00),
(3, 'Vihaan', 'Gupta', 'Bangalore', 'Finance', 48000.00),
(4, 'Saisha', 'Singh', 'Kolkata', 'IT', 52000.00),
(5, 'Arnav', 'Chopra', 'Chennai', 'Marketing', 49000.00),
(6, 'Aadhya', 'Kumar', 'Hyderabad', 'Sales', 48000.00),
(7, 'Advait', 'Malhotra', 'Pune', 'IT', 53000.00),
(8, 'Ananya', 'Verma', 'Ahmedabad', 'Finance', 51000.00),
(9, 'Reyansh', 'Yadav', 'Jaipur', 'Marketing', 48000.00),
(10, 'Riya', 'Gupta', 'Lucknow', 'HR', 54000.00),
(11, 'Ishaan', 'Shah', 'Surat', 'Sales', 50000.00),
(12, 'Kavya', 'Mishra', 'Nagpur', 'Finance', 49000.00),
(13, 'Aarush', 'Singh', 'Indore', 'Marketing', 52000.00),
(14, 'Ishani', 'Jain', 'Kanpur', 'HR', 51000.00),
(15, 'Vivaan', 'Saxena', 'Bhopal', 'Sales', 53000.00);

Output:

Step 4: Update for single row and single column:

Update Employees set FirstName='Poki'
where EmployeeID=1;

Output:

As shown in the output below here single column that is FirstName of the table has been updated instead of all the columns in a single row.

Step 5: Update single row multiple columns:

Update Employees set FirstName='PokiMon', LastName='Anthony' ,
City='Pune',Department='HR', Salary=47000.00
where EmployeeID=1;

Output:

As shown in the output below here all the columns of the single row have been updated.

Batch Update Examples

Step 5: Batch update

The syntax for batch update in SQL typically involves using the UPDATE statement with specific conditions to update multiple records at once. Here’s a general outline of the batch update syntax:

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

• table_name is the name of the table you want to update.
• column1, column2, etc., are the columns you want to update.
• value1, value2, etc., are the new values you want to set for the specified columns.
• condition specifies the criteria for selecting the rows to be updated. Only rows that meet the condition will be updated.

Example 1: Performing Batch Update to Increase Salary

Here we will increase the salary of all the employee by 5% whose salary is less than 50000. To update the salary column in employee of all the employee whose salary is less than 50000 we do batch update. So we do need not to manually check the id of employee whose salary is less than 50000.

UPDATE Employees
SET Salary = Salary * 1.05
WHERE Salary < 50000;

In the above query UPDATE is a DML (Data Manipulation Language) query to update the data in the table.

• Employee is the name of the table that we have created above.
• SET is the keyword to set value.
• Salary = Salary * 1.05 This statement modifies the previous salary with 5%hike.
• Where: It is the keyword used for the condition.
• Salary <50000: Salary is the column name that whose value is checked whether it is below 50000 or not.

Output:

As shown in the output below all the rows whose salary was less than 50k have been increase by 5%.

Explanation: These two consecutive SQL statements increase the salary by 5% for employees whose current salary is less than 50,000. The UPDATE command modifies the Salary column in the Employees table accordingly for eligible records.

Example 2: Batch update of the city

UPDATE Employees
SET City = 'Muzaffarpur'
WHERE Department = 'HR';

Output:

Batch update

Explanation: This SQL statement updates the City column for employees in the HR department, setting their city to ‘Muzaffarpur‘. It modifies the City column values only for records where the Department is ‘HR‘.

Example 3: Batch Update of Salary for Top 6 Rows

UPDATE TOP (6) Employees
SET Salary = 61000;

Output:

Explanation: This SQL statement updates the Salary column for the top 6 employees, setting their salary to 61,000. It modifies the Salary column values only for the top 6 records based on the specified criteria.

Example 4: Batch update of all rows

UPDATE Employees
SET City = 'Unknown';

Output:

As shown in the output below here all the city rows have been changed to Unknown.

Explanation: This SQL statement updates the City column for all employees, setting their city to ‘Unknown‘. It modifies the City column values for every record in the Employees table, effectively changing all city entries to ‘Unknown’.

Conclusion

Batch update is used to update the large numbers of data in a data. It reduces the time to update multiple records in a large set of databases. It includes multiple update queries in a single operation to update the records. It enhances the performance and reduces the lock contention. It is one of the best techniques to update multiple records as it maintains data consistency and integrity.