Update from Select Statement using SQL Server

Overview

SQL Server supports several Data Manipulation Language (DML) commands. It is used to store, retrieve, modify,insert, delete, and update data in database. Four main DML commands are:

1. SELECT- To retrieve data from a table
2. INSERT- to insert data into a table
3. DELETE- to delete the entries from a table
4. UPDATE- To update existing records in a table

Suppose we want to update the data using records already stored in some other table. How is it possible to UPDATE from SELECT statement using SQL Server. The below article will be discussing about the same in details.

Update with select SQL Server Example

To demonstrate the usage of UPDATE from SELECT statement, we need to create two tables.User can update the data in one table using data already stored in another table.We will use UPDATE command and SELECT command.

Table Creation

To create First Table:

CREATE TABLE FirstTable(Column1 INT, Column2 INT, Column3 VARCHAR(60))
INSERT INTO FirstTable(Column1, Column2, Column3)
SELECT 1, 11,'Eleven'
UNION ALL
SELECT 2, 12,'Twelve'
UNION ALL
SELECT 3, 13,'Thirteen'
UNION ALL
SELECT 4, 14,'Fourteen'
GO
Select * from First Table

FirstTable-

Similarly, we will create Second Table:

CREATE TABLE SecondTable (Column1 INT, Column2 INT, Column3 VARCHAR(60))
INSERT INTO SecondTable (Column1, Column2, Column3)
SELECT 1, 21,'Twenty-One'
UNION ALL
SELECT 2, 22,'Twenty-Two'
UNION ALL
SELECT 3, 23,'Twenty-Three'
UNION ALL
SELECT 4, 24,'Twenty-Four'
GO
Select * from SecondTable

SecondTable-

After creating two tables, we insert values on each column of two tables after defining its data types. We have use SELECT command and UNION command to put the values of one row together. The values of the two tables, FirstTable and Second Table can be checked using SELECT command as shown above.

Update Data in FirstTable Using Values Stored in SecondTable

Let us say that we want to update Column2 and Column3 of FirstTable with the values of Column2 and Column3 stored in SecondTable.

In the above diagram, we can see from the arrow that values of Column2 and Column3 of SecondTable will be updated to FirstTable Column2 and Column3. Entries we want to update from one table to another are:

13 → 23; Thirteen → Twenty-Three

14 → 24; Fourteen →Twenty-Four

The easiest way is to use JOIN clause in the UPDATE statement, use multiple tables in the UPDATE statement, and do the task.

UPDATE FirstTable
SET Column2 = t2.Column2,
Column3 = t2.Column3
FROM FirstTable t1
INNER JOIN SecondTable t2 ON t1.Column1 = t2.Column1
WHERE t1.Column1 IN(3, 4)
GO

Select * from FirstTable

Select * from SecondTable

Here, we can see that the two columns of First Table have been updated from the second table. To update the data entries, we have used JOIN clause in UPDATE statement. Later, we can verify the contents of both the tables using SELECT command.