How to UPDATE table from a SELECT in SQL Server

When do you need to think of it?

Sometime you might end up in a situation where you require updating a column with value from another table column value in SQL Server. For example, you have an employee table data and you have worked on the information and amended some data for few employees. Then, you somehow managed to load those amended data into another temporary table in the same database or different database in the same instance.

Now the million dollar question comes into your mind, how to update the amended values into original table so that your application is up-to-date. Everyone loves simple solution of a simple question like how do I update table from select in SQL Server or how to update data in one table from corresponding data in another table in SQL Server.

So we need to find out a tsql to update rows (all columns or less) data from another table using ID in SQL Server.

In most of the cases, SQL updates are performed using a particular table (UPDATE books SET books.title = 'The Hobbit' WHERE books.id = 1). Yet, on occasion, it may prove beneficial to alter the contents of a table indirectly, by using a subset of data obtained from secondary query statement.

Performing an UPDATE using a secondary SELECT statement can be accomplished in one of two ways, primarily depending upon which version of SQL Server you are using. We’ll briefly explore both options so you can find what works best for you.

SQL server Supported versions:

Below I elaborate how you can achieve this and this solution is applicable for SQL Server Express version and all other SQL server versions like SQL server 2005, SQL server 2008, SQL Server 2008R2, SQL server 2012, SQL server 2014 and SQL Server 2016.

Solution Approach 1:

In SQL Server, it’s possible to INSERT INTO a table using a SELECT statement like below

INSERT INTO [Emp].[dbo].[Employee]

SELECT * from [Emp].[dbo].[EmployeeTemp]

But this will try to insert all rows into Employee table from EmployeeTemp table. Remember of any constraint violation during this insertion.

But when you require updating values for existing rows, then use the following method. This is the answer of How to UPDATE table from a SELECT in SQL Server. This is a simple example of how to update from select in SQL server Using INNER JOIN

UPDATE

TableOne

SET

   TableOne.col1 = TableAnother.col1,

   TableOne.col2 = TableAnother.col2

FROM

   One_Table AS TableOne

   INNER JOIN Other_TableAS TableAnother

       ON TableOne.id = TableAnother.id

WHERE

    TableOne.col3 = 'ConditionalValue'

Let’s take an example and walkthrough the query what it does.

I have a table named [Employee] with below data.

I have my updated data in another table called “EmployeeTemp” as below, where TransportAllowance is now updated for the city Oxford.

Now I want to update TransportAllowance for City Oxford. So I prepared the following query

UPDATE

   e

SET

   e.TransportAllowance = et.TransportAllowance

FROM

   [Emp].[dbo].[Employee] AS e

   INNER JOIN [Emp].[dbo].[EmployeeTemp] AS et

       ON e.EmployeeID = et.EmployeeID

WHERE

    e.City = 'Oxford'

Below is the execution result.

Now result set view is what was expected.

Solution Approach 2:

The same can be achieved using MERGE statement. The generic query will look like below:

MERGE INTO One_Table TableOne

  USING Other_Table TableAnother

     ON TableOne.id = TableAnother.id

        AND TableOne.col3 = 'ConditionalValue'

WHEN MATCHED THEN

  UPDATE

     SET   TableOne.col1 = TableAnother.col1,

                    TableOne.col2 =TableAnother.col2

For my example, it would be

MERGE INTO [Emp].[dbo].[Employee] e

  USING [Emp].[dbo].[EmployeeTemp] et

     ON e.EmployeeID = et.EmployeeID

        AND e.city = 'Oxford'

WHEN MATCHED THEN

  UPDATE

      SET e.TransportAllowance = et.TransportAllowance;