How to Update Multiple Records Using One Query in SQL Server?

To update multiple records in the SQL server, use the UPDATE Statement. Using the SQL UPDATE statement with the WHERE clause users can update multiple records in the table based on some condition.

There can be two cases in this situation:

We will cover both these cases in detail below using examples.

Update Multiple Records Based on One Condition in SQL Server

Updating multiple records based on one condition means that all values will be tested against one condition, and depending on that the values will be updated.

For Example, updating the “Results” column to pass if a student gets more than 50 marks.

Syntax

To update multiple records of a table based on a single condition in an SQL server, use this syntax:

UPDATE table_name
SET column_name = value
WHERE condition;

As you can see, we can update multiple values of a column in SQL server using an UPDATE statement with a WHERE clause.

Update Multiple Records Based on Multiple Condition in SQL Server

Sometimes, we need to update values of a column based on multiple conditions.

For example, updating salary of employees based on their performance factor. Employees with performance factor above 30 gets 20% salary hike, performance factor above 50 gets 30% hike and employees below 20 will only get only 9% hike.

We can not use the simple UPDATE statement with WHERE clause in this case, as we will need to use multiple UPDATE and WHERE statements. In this case, we can use the CASE clause with WHERE clause creating multiple conditions

Syntax

To update multiple records of a table based on multiple conditions in SQL server, use this syntax:

UPDATE table_name
SET column_value = CASE column_name
WHEN ‘column_name1’ THEN column_value1
WHEN ‘column_name2’ THEN column_value2
ELSE column_value
END
WHERE column_name IN(‘column_name1’, ‘column_name2’);

As you can see, to update value of a column based on multiple conditions, use the CASE clause with WHERE clause, creating multiple conditions inside a single statement and updating the values on multiple conditions.

Update Multiple Records in SQL Server Examples

Let’s look at some examples of how to update multiple records of a table in SQL server. We will look at example of both cases and understand their working.

Demo Table

Before looking at the examples, let’s create a DEMO database and table, which will be used in examples.

CREATE DATABASE GeeksForGeeks;
USE GeeksForGeeks
CREATE TABLE BANDS(
BAND_NAME VARCHAR(20),
PERFORMING_COST INT,
NUMBER_OF_MEMBERS INT);
INSERT INTO BANDS (name, followers, members)
VALUES
  ('INDIAN OCEAN', 10000, 5),
  ('BTS', 20000, 6),
  ('METALLICA', 30000, 10),
  ('BEATLES', 40000, 4),
  ('EAGLES', 50000, 4);

Update Multiple Records Based on One Condition in SQL Server Example

In this example, we will update all records of the table BANDS satisfying only a single condition. The condition here is that the value of the column NUMBER_OF_MEMBERS should be less than 5. If the condition is satisfied then the value of the column PERFORMING_COST doubles itself.

Query:

UPDATE BANDS 
SET PERFORMING_COST = 2*PERFORMING_COST 
WHERE NUMBER_OF_MEMBERS<=5;

Output:

Update Multiple Records Bsed on Multiple Condition in SQL Server Example

In this example, we will update all records of the table BANDS satisfying two(multiple) conditions. The condition here is if the BAND_NAME is ‘METALLICA’, then its PERFORMING_COST is set to 90000 and if the BAND_NAME is ‘BTS’, then its PERFORMING_COST is set to 200000. Use the keyword UPDATE and WHEN to achieve this. This query behaves like an if-else if-else block.

Query:

UPDATE BANDS
SET PERFORMING_COST = CASE BAND_NAME
WHEN 'METALLICA' THEN 90000
WHEN 'BTS' THEN 200000
ELSE PERFORMING_COST
END
WHERE BAND_NAME IN('METALLICA', 'BTS');

Output:

Conclusion

To update multiple records in a table, we can use the UPDATE statement combined with a WHERE clause. In this article, we have explored two scenarios of updating multiple records in SQL Server, complete with examples. Users can efficiently update multiple records based on a single condition or multiple conditions.

This method proves particularly useful in salary databases when there’s a need to adjust employee salaries based on performance metrics.