Update From Select Statement in SQL Server

One of my programmer asked “What’s the simplest way to update some values from one table to another table?“. I told him to use the UPDATE FROM statement. UPDATE FROM is nothing but, using the UPDATE statement along with the SELECT statement using the FROM clause. Here is how we can use the update from select statement. There are several ways to use select statement in update query. Let’s look at them one by one.

To start with, we’ll create two tables with some data in them. Here is the table creation and the data insertion scripts.

/* Creating First table */
CREATE TABLE [dbo].[MyTecBits_Table_1](
[Sl_no] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Description] [varchar](500) NULL
) ON [PRIMARY]
GO

insert into MyTecBits_Table_1 values ('Name One','Description One')
insert into MyTecBits_Table_1 values ('Name Two','Description Two')
insert into MyTecBits_Table_1 values ('Name Three','Description Three')
insert into MyTecBits_Table_1 values ('Name Four','Description Four')

/* Creating Second table */
CREATE TABLE [dbo].[MyTecBits_Table_2](
[Sl_no] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Description] [varchar](500) NULL
) ON [PRIMARY]
GO

insert into MyTecBits_Table_2 values ('1st Name','1st Description')
insert into MyTecBits_Table_2 values ('2nd Name','2nd Description')
insert into MyTecBits_Table_2 values ('3rd Name','3rd Description')
insert into MyTecBits_Table_2 values ('4th Name','3rd Description')

Now, run the select query to check the sample data in the tables.

Select * from MyTecBits_Table_1;
Select * from MyTecBits_Table_2;

Since the environment (tables and data) is ready, we will move on to the experiments.

1. Update FROM Select Statement

Consider, that we need to update Name and Description columns of all the rows from the first table MyTecBits_Table_1 with the Name and Description columns from the second table MyTecBits_Table_2 with the similar Sl_no. Here is the simple update with select statement to update the data.

Update MyTecBits_Table_1
Set Name = b.Name, Description = b.Description
From MyTecBits_Table_2 as b Inner Join MyTecBits_Table_1 as a
	On a.Sl_no = b.Sl_no

In this case, we are joining the two tables using the identity column. Then updating the first table using FROM clause having the inner join between tables. This is a simple update query with FROM select statement.

2. Update FROM Select Using WHERE Clause

Now, we will complicate the scenario a bit. Consider that we need to update the Name and Description column of the table MyTecBits_Table_1 having Sl_no greater than 2 with the equivalent Sl_no column value in MyTecBits_Table_2. In this case, we have to use the WHERE clause in the select statement. So, we have to modify the above specified update query by adding a WHERE clause to apply the filter condition.

Update MyTecBits_Table_1
Set Name = b.Name, Description = b.Description
From MyTecBits_Table_2 as b Inner Join MyTecBits_Table_1 as a
	On a.Sl_no = b.Sl_no
	Where a.Sl_no > 2

3. Update using MERGE

Another way is to update some values from one table to another table using the MERGE T-SQL operation. MERGE is available from SQL Server 2008. Here is an example to get the same result as the previous (WHERE Clause) method.

MERGE INTO MyTecBits_Table_1 T1
   USING MyTecBits_Table_2 T2
      ON T1.Sl_no = T2.Sl_no
	AND T1.Sl_no > 2
WHEN MATCHED THEN
   UPDATE 
      SET Name = T2.Name, 
          Description = T2.Description;

4. Update FROM Select Query: Same Table

Consider, that we need to update the values of certain rows in a table with the values for some other rows from the same table. In this case, we need to use the same table with inner join in the FROM statement.

For example, if I want to update the Description column value in table MyTecBits_Table_1 where Name as “Name Two” with the corresponding Description from the same table where “Name Four”, then I’ve to write the update statement as:

Update a
Set a.Description = b.Description
From MyTecBits_Table_1 as a Inner Join MyTecBits_Table_1 as b
	On a.Name = 'Name Two' and b.Name = 'Name Four'

5. Update With Select Sub Query: Same Table

An alternative way for the above mentioned joining same table is to use a select statement as sub query. In this case, we have to rewrite the above query as:

Update MyTecBits_Table_1
Set Description = (Select Description From MyTecBits_Table_1 Where Name = 'Name Four')
Where  Name = 'Name Two'

Thus, the simplest and straightforward way to update values from one table to another table is to use the UPDATE FROM SELECT statement. By using UPDATE FROM, you can avoid the complicated ways like cursors, table data type, temp table, etc.

Related Article

• Joining tables from multiple databases.
• How to update if row exists else insert Using MERGE operation.

Reference

• More about UPDATE (Transact-SQL) at Microsoft Docs.
• More about MERGE (Transact-SQL) at Microsoft Docs.