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 w3wiki;
USE w3wiki
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:
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:
Table of Content
- Update Multiple Records Based on One Condition in SQL Server
- Update Multiple Records Based on Multiple Condition in SQL Server
We will cover both these cases in detail below using examples.