How to UPDATE a Record Using a SELECT in SQL Server

1. Introduction

When data is split across multiple tables, we often need to update one table using values from another. It usually occurs when one table stores the main data and another table receives updates that may be from a sync, import, or migration. In these cases, a SELECT statement often retrieves the relevant values to apply the update.

In this tutorial, we’ll explore the concept of updating records with a SELECT in SQL Server. To perform this task in SQL Server, an UPDATE selects values from another table using subqueries, JOINs, or MERGE. To demonstrate, we’ll use the Baeldung University schema. In particular, the Course table will serve as the main target, with new details sourced from the Department table.

2. Update Records Using a Subquery

In SQL Server, a common method to update a record is through a subquery. Specifically, the subquery involves a SELECT statement to find the required value from another table. Once the data is fetched, the UPDATE statement sets the value in the target table.

Let’s see a query that updates each course in the Course table. If the related Department has the code CS, it sets the is_active field to Yes; otherwise, to No:

UPDATE Course
SET is_active = (
    SELECT
        CASE
            WHEN Department.code = 'CS' THEN 'Yes'
            ELSE 'No'
        END
    FROM Department
    WHERE Department.id = Course.department_id
);

This query sets is_active to Yes for CS and No for others.

Let’s check out the resulting table:

+----------+------------------------------------+-----------+---------------+-----------------------------+---------+
| ID       | Name                               | Is Active | Department ID | Textbook                    | Credits |
+----------+------------------------------------+-----------+---------------+-----------------------------+---------+
|   CS111  | Introduction to Operating Systems  | Yes      |  1            | OS by Tanenbaum             | 7       |
...
|   EC111  | Principles of Communication        | No       |  2            | Principles of Communication | 7       |
...
|   ME111  | Advanced Dynamics: Introduction    | No       |  3            | Advanced Dynamics by Donald | 7       |
...
+----------+------------------------------------+-----------+---------------+-----------------------------+---------+

Notably, this method is only effective for small datasets because SQL Server runs the subquery separately for every row, which slows down the performance. To handle larger updates more effectively, we can use JOIN or MERGE instead.

3. Update Records With JOIN

An efficient approach to update records is to utilize JOIN. To demonstrate, let’s consider the same scenario of updating the Course.is_active field based on the related Department.code.

First, we create a query for the JOIN method:

UPDATE Course
SET is_active =
    CASE WHEN Department.code = 'EC' THEN 'Yes' ELSE 'No' END
FROM Course
JOIN Department ON Course.department_id = Department.id;

This update query successfully marks courses with code EC as active, giving the expected result:

+----------+------------------------------------+-----------+---------------+-----------------------------+---------+
| ID       | Name                               | Is Active | Department ID | Textbook                    | Credits |
+----------+------------------------------------+-----------+---------------+-----------------------------+---------+
|   CS111  | Introduction to Operating Systems  | No       |  1            | OS by Tanenbaum             | 7       |
...
|   EC111  | Principles of Communication        | Yes      |  2            | Principles of Communication | 7       |
...
|   ME111  | Advanced Dynamics: Introduction    | No       |  3            | Advanced Dynamics by Donald | 7       |
...
+----------+------------------------------------+-----------+---------------+-----------------------------+---------+

Here, the JOIN method links both tables in a single step. Thus, the update happens faster by letting the database engine match rows in bulk instead of doing repeated lookups.

4. Update Records Through MERGE

MERGE is a powerful feature in SQL Server for data synchronization. Unlike JOIN or subqueries, which typically handle one task at a time, MERGE can UPDATE existing records, INSERT new ones, and DELETE outdated data in a single, atomic step.

Let’s explore an example:

MERGE Course AS Target
USING (
    SELECT id, code
    FROM Department
) AS Source
ON Target.department_id = Source.id
WHEN MATCHED THEN
    UPDATE SET
        Target.is_active =
            CASE
                WHEN Source.code = 'ME' THEN 'Yes'
                ELSE 'No'
            END;

Here, MERGE updates the is_active field for all Course records whose department_id matches an id in the Department table.

Let’s see the output:

+----------+------------------------------------+-----------+---------------+-----------------------------+---------+
| ID       | Name                               | Is Active | Department ID | Textbook                    | Credits |
+----------+------------------------------------+-----------+---------------+-----------------------------+---------+
|   CS111  | Introduction to Operating Systems  | No        |  1            | OS by Tanenbaum             | 7       |
...
|   EC111  | Principles of Communication        | No        |  2            | Principles of Communication | 7       |
...
|   ME111  | Advanced Dynamics: Introduction    | Yes       |  3            | Advanced Dynamics by Donald | 7       |
...
+----------+------------------------------------+-----------+---------------+-----------------------------+---------+

In the current example, the logic processes only matched records. However, the MERGE statement also handles unmatched scenarios and enables the insertion or deletion of rows when necessary. This approach makes it easy to handle complex conditions and keep dependent tables consistent without writing multiple separate statements.

5. Conclusion

In this article, we explored three ways to update records using a SELECT in SQL Server. Each method offers a unique benefit.

Subqueries provide a simple structure, JOIN improves performance for bulk updates, and the MERGE method provides a flexible solution for complex scenarios when we need to update, insert, or delete records in a single operation. The right choice depends on the update scenario and query complexity requirements.

The queries discussed here are available over on GitHub.