In this article we will show you, How to write a SQL Query to UPDATE columns in a table using the SELECT Statement in SQL Server with example. This is one of the SQL Server Frequently Asked Question. For this example, We are going to use the below shown data
Below table will show the data present in Employee Duplicate table. Our task is to update the columns (firstname, lastname, and Yearly Income) in this table with the above specified table.
How to UPDATE from SELECT in SQL Server Example 1
In this example we will show you, How to update from select statement using the Subquery.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 |
-- Query to UPDATE from SELECT in SQL Server USE [SQL Tutorial] GO UPDATE [EmployeeDuplicates] SET [YearlyIncome] = ( SELECT [YearlyIncome] FROM [Employee] WHERE [Employee].EmpID = [EmployeeDuplicates].EmpID) GO |
OUTPUT
Now let me show you the Updated table
How to UPDATE from SELECT in SQL Server Example 2
Above specified example might be a good option to update single column. In this example we will show you, How can I do an UPDATE statement with JOIN in SQL Server.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Query to UPDATE from SELECT in SQL Server USE [SQL Tutorial] GO 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 |
OUTPUT
Now let me show you the Updated table
How to UPDATE from SELECT in SQL Server Example 3
In this example we will show you, How to update the table columns using the Select statement. Here, we are using the Subquery at FROM statement
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- Query to UPDATE from SELECT in SQL Server USE [SQL Tutorial] GO 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 |
OUTPUT
Now let me show you the Updated table
How to UPDATE from SELECT in SQL Server Example 4
In this example we will show you, How to update the table columns from the Select statement using the MERGE Statement
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Query to UPDATE from SELECT Statement in SQL Server USE [SQL Tutorial] GO 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]; |
OUTPUT
Now let me show you the Updated table
Thank You for Visiting Our Blog
Leave a Reply