SQL Server: Update From Select Statement

One of my programmer asked me “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;

Update From Select

Since the tables and data are ready, we will move on to the experiments.

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 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

Update From Select Result

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.

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

Update From Select With Where

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'

Update From Select Same Table

Update With Select Sub Query

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'

Update With Select Sub-Query

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

Reference

Leave your thoughts...