Update From Another Table Sql Server

How to Update Rows in SQL Server Using Data from Another Table

When you need to synchronize or copy values from one table to another, the UPDATE statement combined with a subquery or JOIN is the most efficient method in SQL Server. This technique is often referred to as “update from another table sql server” and is essential for data consolidation, batch processing, and maintaining referential integrity across related tables Small thing, real impact..

Below, you’ll find a step‑by‑step guide, practical examples, and best practices that will help you master this operation while keeping your queries clean and performant.

Why Use an Update from Another Table?

• Data Consolidation: Merge information stored in separate tables without manual copying.
• Batch Corrections: Apply bulk changes based on criteria defined in a reference table.
• Maintain Consistency: check that related tables reflect the same master data.

By leveraging a subquery or a JOIN, you can update a target table’s columns with values from a source table in a single statement, reducing the need for multiple scripts or application logic Small thing, real impact..

Setting Up the Scenario

Before diving into the syntax, let’s outline a common scenario:

• Target Table: Employees (contains columns EmployeeID, Salary, DepartmentID)
• Source Table: SalaryAdjustments (contains columns EmployeeID, NewSalary, AdjustmentDate)

The goal is to update the Salary column in Employees with the NewSalary from SalaryAdjustments where the EmployeeID matches and the adjustment is recent (e.g., within the last 30 days) But it adds up..

Create Sample Data

-- Target table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Salary DECIMAL(10,2),
    DepartmentID INT
);

-- Source table
CREATE TABLE SalaryAdjustments (
    AdjustmentID INT PRIMARY KEY,
    EmployeeID INT,
    NewSalary DECIMAL(10,2),
    AdjustmentDate DATETIME
);

Insert a few rows for demonstration:

INSERT INTO Employees (EmployeeID, Salary, DepartmentID) VALUES
(1, 5000.00, 10),
(2, 6000.00, 20),
(3, 7000.00, 30);

INSERT INTO SalaryAdjustments (AdjustmentID, EmployeeID, NewSalary, AdjustmentDate) VALUES
(100, 1, 5500.00, GETDATE() - 10),
(101, 2, 6200.00, GETDATE() - 40),
(102, 3, 7500.

---

## Method 1: Update Using a Subquery

A subquery allows you to fetch the source value directly within the **`UPDATE`** statement. This approach is straightforward when you have a simple relationship between the tables.

### Syntax

```sql
UPDATE target_table
SET    target_column = (SELECT source_column
                       FROM   source_table
                       WHERE  source_table.EmployeeID = target_table.EmployeeID
                       AND    source_table.AdjustmentDate >= DATEADD(day, -30, GETDATE()))
WHERE  EXISTS (SELECT 1
               FROM   source_table
               WHERE  source_table.EmployeeID = target_table.EmployeeID
               AND    source_table.AdjustmentDate >= DATEADD(day, -30, GETDATE()));

Explanation

1. Inner SELECT retrieves the NewSalary from SalaryAdjustments for the matching EmployeeID and recent date.
2. Outer UPDATE modifies the Salary column in Employees.
3. The WHERE EXISTS clause ensures we only update rows that have a corresponding recent adjustment.

Applying to Our Example

UPDATE Employees
SET    Salary = (SELECT NewSalary
                FROM   SalaryAdjustments
                WHERE  SalaryAdjustments.EmployeeID = Employees.EmployeeID
                AND    SalaryAdjustments.AdjustmentDate >= DATEADD(day, -30, GETDATE()))
WHERE  EXISTS (SELECT 1
               FROM   SalaryAdjustments
               WHERE  SalaryAdjustments.EmployeeID = Employees.EmployeeID
               AND    SalaryAdjustments.AdjustmentDate >= DATEADD(day, -30, GETDATE()));

After execution, only Employee 1 and Employee 3 receive updated salaries (Employee 2’s adjustment is older than 30 days).

Method 2: Update Using an INNER JOIN

When you need more control over the join conditions or want to update multiple columns, an INNER JOIN is often clearer and can improve readability Worth knowing..

Syntax

UPDATE target_table t
SET    t.target_column = s.source_column
FROM   target_table t
INNER JOIN source_table s
        ON s.EmployeeID = t.EmployeeID
WHERE  s.AdjustmentDate >= DATEADD(day, -30, GETDATE());

Explanation

• FROM target_table t gives the target table an alias t.
• INNER JOIN source_table s links the source table based on the key column(s).
• The SET clause references columns from both aliases.
• The WHERE clause filters adjustments by date.

Applying to Our Example

UPDATE Employees e
SET    e.Salary = s.NewSalary
FROM   SalaryAdjustments s
WHERE  s.EmployeeID = e.EmployeeID
  AND  s.AdjustmentDate >= DATEADD(day, -30, GETDATE());

This single statement updates the salaries for qualifying employees, mirroring the results of the subquery method Small thing, real impact..

Best Practices for Updating from Another Table

1. Use Transactions
   Wrap the UPDATE in a transaction to roll back changes if something goes wrong:

   BEGIN TRANSACTION;
   -- Your UPDATE statement here
   COMMIT;
   

2. Test with a SELECT First
   Always run a corresponding SELECT to preview the rows that will be affected:

   SELECT e.Even so, adjustmentDate
   FROM   Employees e
   INNER JOIN SalaryAdjustments s
           ON s. But employeeID = e. EmployeeID, e.Plus, newSalary, s. Which means salary, s. EmployeeID
   WHERE  s.
   
   

3. Index Join Columns
   see to it that columns used in the join (EmployeeID) and filter (AdjustmentDate) are indexed. This dramatically speeds up large‑scale updates Worth keeping that in mind..

4. Avoid Updating All Rows
   If you omit a WHERE clause, the statement will update every row in the target table, which can be catastrophic. Always include a filter And that's really what it comes down to..

5. Consider Table Variables for Complex Logic
   For updates that involve multiple source tables or complex calculations, declare table variables or CTEs (WITH ... AS) before the UPDATE:

   ;WITH RecentAdjustments AS (
       SELECT EmployeeID, NewSalary
       FROM   SalaryAdjustments
       WHERE  AdjustmentDate >= DATEADD(day, -30, GETDATE())
   )
   UPDATE e
   SET    e.Here's the thing — newSalary
   FROM   Employees e
   INNER JOIN RecentAdjustments ra
           ON ra. Day to day, salary = ra. EmployeeID = e.
   
   

Common Pitfalls and How to Avoid Them

| Incorrect data types | “Arithmetic overflow” or “Conversion failed” errors. g., Salary and NewSalary) are compatible. | | Non‑deterministic updates | The update runs without error, but the resulting values are inconsistent across executions. Think about it: | Ensure the join condition uniquely identifies one source row per target row. If multiple source rows exist, use an aggregate (e.But , MAX, SUM) or a window function to pick a deterministic value. In practice, g. | Verify that the data types of the columns used in the update (e.Convert explicitly if necessary, but ensure the conversion is lossless. | | Ignoring NULLs in join conditions | Rows that should be updated are skipped because of NULL comparisons.

Continuing the Article

Complete the Pitfalls Table

| Improper Transaction Management | Uncommitted changes may persist after a failure; locks remain held longer than needed. Still, use ROLLBACK if any step fails midway. | | Updating based on volatile functions | Results change between execution attempts, leading to unpredictable behavior. In practice, | Wrap the entire operation in a transaction and always issue COMMIT only after confirming success. Even so, | Keep transactions short—only perform the update and commit. Defer cleanup tasks like logging or archiving to separate processes. | | Long-running transactions | Database performance degrades due to resource locking and memory consumption. | Capture intermediate values in a temporary table or variable before applying them, ensuring consistency.

Additional Best Practices

1. Validate Data Integrity After Update

After executing the UPDATE, run a verification query to confirm the expected rows were modified correctly:

-- Count how many rows were actually changed
SELECT COUNT(*) 
FROM   Employees e
INNER JOIN SalaryAdjustments s
        ON s.EmployeeID = e.EmployeeID
WHERE  s.AdjustmentDate >= DATEADD(day, -30, GETDATE())
  AND NOT EXISTS (SELECT 1 FROM Employees e2 
                  WHERE e2.EmployeeID = e.EmployeeID 
                    AND e2.Salary = e.Salary);  -- optional sanity check

If the count does not match your expectations, investigate why some rows were missed or incorrectly altered.

2. Use OUTPUT Clause for Audit Trails

Modern SQL Server versions support the OUTPUT clause, allowing you to capture the rows that were changed directly within the UPDATE statement. This eliminates the need for a separate SELECT after the fact:

BEGIN TRANSACTION;

UPDATE e
SET    e.Salary = s.NewSalary
FROM   Employees e
JOIN   SalaryAdjustments s
       ON e.EmployeeID = s.EmployeeID
WHERE  s.

-- Capture the updated rows for audit logging
SELECT  e.EmployeeID,
        e.Salary   AS OldSalary,
        s.NewSalary AS NewSalary,
        CAST(e.EmployeeID AS VARCHAR(10)) AS PrimaryKey
INTO   dbo.AuditLog_RecentAdjustments
FROM   Employees e
JOIN   SalaryAdjustments s
       ON e.EmployeeID = s.EmployeeID
WHERE  s.AdjustmentDate >= DATEADD(day, -30, GETDATE());

COMMIT;

The AuditLog_RecentAdjustments table can later be queried to generate reports, monitor compliance, or trigger alerts when salary changes exceed certain thresholds.

3. Handle Concurrency Safely

If other sessions modify the same rows concurrently, a plain UPDATE may result in lost updates. To mitigate this, consider adding a version column or using optimistic concurrency control:

ALTER TABLE Employees ADD Version INT NOT NULL DEFAULT 0;
ALTER TABLE SalaryAdjustments ADD Version INT NOT NULL DEFAULT 0;

Then modify the update to include a version check:

UPDATE e
SET    e.Salary = s.NewSalary
FROM   Employees e
JOIN   SalaryAdjustments s
       ON e.EmployeeID = s.EmployeeID
WHERE  s.AdjustmentDate >= DATEADD(day, -30, GETDATE())
  AND  (SELECT Version INTO v FROM SalaryAdjustments sa 
         WHERE sa.EmployeeID = e.EmployeeID 
           AND sa.AdjustmentDate >= DATEADD(day, -30, GETDATE()) 
           AND sa.Version = e.Version);

Alternatively, wrap the operation in a snapshot-based transaction and apply ROWVERSION column for automatic tracking.

4. Performance Monitoring

Before deploying a bulk update, benchmark its impact on system load:

EXEC sp_MSforeachtable 'SELECT @RowsAffected = @@ROWCOUNT;';

Or use Extended Events to track duration, CPU usage, and blocking activity during the operation. For very large datasets, consider processing in batches: