SQL Server Update From One Table to Another Based on an ID Match

In the world of database management, we need to perform various OLTP operations like insert, update, and delete. The ability to efficiently update data between tables is crucial for maintaining data integrity and ensuring accurate information. SQL Server provides powerful tools to accomplish this task, offering developers various methods to update data seamlessly. In this article, we’ll explore how to update data from one table to another in SQL Server, covering different scenarios and best practices.

SQL Server Update From One Table to Another Based on an ID Match

Updating data from one table to another based on an ID match in SQL Server means modifying records in a destination table by replacing them with corresponding records from a source table where the IDs match. This operation is often necessary when you have two tables with related information, and you want to synchronize or merge the data between them.

Understanding the Update Statement

The UPDATE statement in SQL Server is used to modify existing records in a table. Its basic syntax is:

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

This statement updates the specified columns with the provided values based on the specified condition. However, when updating data from one table to another, we need to utilize additional techniques, such as JOINs.

For our scenario, we need to implement the joins.

Understanding the JOINS

In SQL Server, a JOIN is a clause used to combine rows from two or more tables based on a related column between them. It allows you to retrieve data from multiple tables simultaneously, enabling you to create more complex queries that involve data from different sources. There are different types of joins like inner join, left outer join, right outer join, etc. Read more about joins here.

For this article, we need inner join which returns rows when there is at least one match in both tables based on the join condition.

Its syntax is as below.

SELECT * FROM table1 INNER JOIN table2 ON table1.field1 = table2.field2

As we have understood the basic concepts required for our journey, so let’s start with the example to get a deeper understanding.

We need SQL Server, SSMS, and basic understanding of the SQL queries.

Example

Imagine we’re managing the employee records for GeeksForGeeks. We’re focusing on two specific tables: one for the current year’s salary data and another for the previous year’s salary data. These tables are named CurrentYearData and PreviousYearData respectively. Now, for some business-related tasks, we need to ensure that the salary information in the PreviousYearData table is updated with the latest salary data from the CurrentYearData table.

To get started, let’s create and populate these tables.

Step 1

Let’s create two tables as discussed above CurrentYearData which stores information about employee like ID, name, salary, joining data,, and department. And another table PreviousYearData which stores information like employee id, previous salary, and remarks.

So below is the query.

USE GeeksForGeeks

CREATE TABLE CurrentYearData (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Salary DECIMAL(10, 2),
    Department VARCHAR(100),
    JoinDate DATE
);


CREATE TABLE PreviousYearData (
    EmployeeID INT PRIMARY KEY,
    PreviousSalary DECIMAL(10, 2),
    Remarks VARCHAR(255)
);

Output:

Figure 1: We have created our two tables.

Explanation: This is the simple query to create two tables, do notice that EmployeeID is the primary key for both the tables and it is used to establish the relation between them and employee name is not mentioned in second table as it will be fetched using the join on the employeeID.

Step 2

Let’s add data into our tables on which we will perform some actions to understand the concepts.

Query:

INSERT INTO CurrentYearData (EmployeeID, Name, Salary, Department, JoinDate)
VALUES
    (1, 'John Doe', 600000, 'IT', '2023-01-05'),
    (2, 'Jane Smith', 720000, 'HR', '2022-09-15'),
    (3, 'Alice Johnson', 550000, 'Marketing', '2023-03-20'),
    (4, 'Bob Williams', 650000, 'Finance', '2022-11-10'),
    (5, 'Emily Brown', 600000, 'IT', '2023-02-10'), 
    (6, 'Michael Clark', 700000, 'Finance', '2023-04-25'), 
    (7, 'Sophia Lee', 620000, 'Marketing', '2023-06-15'); 


SELECT * FROM CurrentYearData

For PreviousYearData table use below query to insert data and select data.

INSERT INTO PreviousYearData (EmployeeID, PreviousSalary, Remarks)
VALUES
    (1, 550000, 'Performance bonus awarded'),
    (2, 700000, 'Salary hike due to promotion'),
    (3, 520000, 'Annual increment'),
    (4, 600000, 'Contract renewal with revised terms');

SELECT * FROM PreviousYearData

Output:

Figure 2: We can see the inserted data.

Records inserted in the second table:

Figure 3: Records inserted in the second table too.

Explanation: We have written query to insert the data into the tables and using select on the same table we can verify the inserted data.

Step 3

Now we will use inner join to find out the common entries in the tables and in business language we can say that we need records of the employees which will give information about employeeId, name, current salary, and previous salary which can be used by HRs to understand the growth of the employee in the company.

Query:

SELECT 
CYD.EmployeeID, 
CYD.Name, 
CYD.Salary, 
PYD.PreviousSalary
FROM CurrentYearData CYD
INNER JOIN
PreviousYearData PYD
ON CYD.EmployeeID = PYD.EmployeeID

Output:

Figure 4: Output of the join data.

Explanation: In the above image we can see only records as only these records are matched between our tables. EmployeeID 1 to 4 are present in the PreviousYearSalary and thus we can these records here. Like any other normal select, in the join query also we can mention the list of the columns and to specifically select from only one table data we can use the table alias and * or columns list. Note that we can use multiple tables for the join as many as can satisfy the join condition.

Step 4

Now we need to see how update statement works. So we will update one records from CurrentYearSalary, we will change the department of the Michael Clark whose employeeId is 6 to IT.

Query

SELECT * FROM CurrentYearData WITH(NOLOCK)
WHERE EmployeeID = 6

UPDATE CurrentYearData WITH (ROWLOCK)
SET Department = 'IT'
WHERE EmployeeID = 6

SELECT * FROM CurrentYearData WITH(NOLOCK)
WHERE EmployeeID = 6

Output:

Figure 5: We can the difference in the output of both select queries.

Explanation: We have written two selects before and after update to verify the before and after value of the specific record. Interesting thing to notice here is the clauses used. We have used WITH(NOLOCK) clause for SELECT and WITH(ROWLOCK) clause for UPDATE statement.

NOLOCK makes sure to select the data even when it is locked by other resources and ROWLOCK makes only row level lock while updating the records which makes sure whole table is not locked. This is done by SQL Server to maintain the ACID properties of the data. And it is the best practice to avoid deadlocks in the database.

Step 5

Here comes the last step because we have already seen the update and joins implementation in previous steps and let’s combine them to solve our problem.

Query:

UPDATE PYD WITH (ROWLOCK)
SET PYD.PreviousSalary = CYD.Salary
FROM PreviousYearData PYD 
INNER JOIN 
CurrentYearData CYD WITH (NOLOCK)
ON PYD.EmployeeID = CYD.EmployeeID

Output:

Figure 6: We have updated 4 records.

Explanation: As our need to was to update one table to another based on ID match, we have used the join to match the ID column and updated the PreviousYearSalary – PreviousSalary column with CurrentYearSalary – Salary column.

Also note that we have used WITH (ROWLOCK) clause for update and we can even use the NOLOCK clause for CurrentYearSalary table as it is used to fetch data. We can even add more tables to add the conditions or update the records from.

Step 6

This is really last step though which will verify the updated records.

Query:

SELECT * FROM PreviousYearData  WITH (NOLOCK)

Output:

Figure 7: We can see that records are updates.

Explanation: This is simple select query on the PreviousYearSalary table to verify the updated records. We can compare the records from Step 2 image to see before update records.

Performing OLTP operations is very tricky thing for database and always keep in mind below points while updating the records.

• Backup Data: Before performing any updates, it’s essential to back up your data to prevent data loss in case of errors.
  Let’s
• Test in a Development Environment: Always test your SQL statements in a development environment before applying them to production data.
• Use Transactions: Wrap your update statements in transactions to ensure atomicity and data consistency.
• Optimize Performance: Consider indexing columns used in JOIN and WHERE clauses to improve query performance, especially for large datasets.
• Using the table hints: It is very important to use the correct clause like NOLOCK and ROWLOCK on the tables while executing the update statement.

Conclusion

Updating data between SQL Server tables is a common task in database management. By leveraging SQL’s powerful UPDATE statement with JOINs, developers can efficiently synchronize and maintain data across multiple tables. Understanding the syntax and best practices outlined in this article will help ensure successful data updates while preserving data integrity and performance.