How to UPDATE from SELECT in SQL Server 5

How to UPDATE from SELECT in SQL Server

How to write a SQL Query to UPDATE columns in a table by using the SELECT statement with an example?. This Update from Select in SQL server is one of the Frequently Asked Questions. For this example, We are going to use the below shown data.

Employee Table Data 1

The below table will show the data present in the Employee Duplicate table. Our task is to update the columns (firstname, lastname, and Yearly Income) in this table with the above-specified table.

Duplicate Employee table records 2

How to UPDATE from SELECT in SQL Server Example?

In this example, we will show you how to update from the select statement using the Subquery.

UPDATE [EmployeeDuplicates]
   SET [YearlyIncome] = ( SELECT [YearlyIncome] 
			  FROM [Employee]
			  WHERE [Employee].EmpID = [EmployeeDuplicates].EmpID)
 
GO
Messages
--------
(14 row(s) affected)

Now let me show you the SQL Server Updated table

How to UPDATE from SELECT in SQL Server 4

How to UPDATE from SELECT Example 2

The above-specified example might be an excellent option to update a single column. In this SQL update select example, let us see how we can make an UPDATE statement with JOIN in SQL Server.

UPDATE [EmpDup]
   SET [EmpDup].[FirstName] = [Emp].[FirstName],
	   [EmpDup].[LastName] = [Emp].[LastName],
	   [EmpDup].[YearlyIncome] = [Emp].[YearlyIncome]
FROM [EmployeeDuplicates] AS [EmpDup]
INNER JOIN [Employee] AS [Emp] 
   ON [EmpDup].EmpID = [Emp].EmpID 
WHERE [EmpDup].EmpID = [Emp].EmpID 
GO
How to UPDATE from SELECT in SQL Server 5

Now let me show you the Updated table

How to UPDATE from SELECT in SQL Server 6

UPDATE SELECT Example 3

In this example, we show you how to update the table columns using the Select statement. Here, we are using the Subquery at FROM statement

UPDATE [EmployeeDuplicates]
   SET [FirstName] = [Emp].[FirstName],
	   [LastName] = [Emp].[LastName],
	   [YearlyIncome] = [Emp].[YearlyIncome]
FROM (SELECT EmpID,
	     [FirstName], 
	     [LastName],
	     [YearlyIncome] 
       FROM [Employee]
     ) [Emp] 
WHERE [EmployeeDuplicates].EmpID = [Emp].EmpID 
GO
How to UPDATE from SELECT in SQL Server 9

Now let me show you the Updated table

How to UPDATE from SELECT in SQL Server 10

How to UPDATE from SELECT Example 4

In this example we will show you, How to update the table columns from the Select statement using the MERGE Statement in Sql Server

MERGE INTO [EmployeeDuplicates] AS [EmpDup]
   USING (
           SELECT EmpID,
		  [FirstName], 
		  [LastName],
		  [YearlyIncome] 
           FROM [Employee] 
         ) [Emp]
   ON [EmpDup].EmpID = [Emp].EmpID
WHEN MATCHED THEN
   UPDATE 
  	SET [EmpDup].[FirstName] = [Emp].[FirstName],
	    [EmpDup].[LastName] = [Emp].[LastName],
	    [EmpDup].[YearlyIncome] = [Emp].[YearlyIncome];

Run the above merge update from select query

Messages
--------
(14 row(s) affected)

Now let me show you the Updated table using this update from select statement

How to UPDATE from SELECT in SQL Server 8