How to UPDATE using SELECT statement in SQL Server

In this article, we will learn how to UPDATE using a SELECT statement in SQL Server. There are “multiple ways which are used to update the data in a given table using data from other table. The UPDATE can be done in simple way or using SELECT query structure. We will discuss both of these in this article.

An UPDATE query is used to update the existing record of table. It can update all records, or we are restrict it to update only few records by adding the “Where” clause in the Update statement.   

UPDATE employee
SET FirstName = ‘John’ , Age = 31 
where Emp_Id =101

Update data Using Joins

In SQL server using below query, we can update the table data by using SELECT statement.

UPDATE
    Emp
SET
    Emp.Age = User.Age,
    Emp .Salary = User.Salary
FROM
    Employee as Emp
    INNER JOIN UserInfo as User
        ON  Emp.id = User.id
WHERE
    Emp.id = 101

Using above query “Employee” table record (age and salary) will get updated using the data from “USER” table. Here only one employee data will get updated because of the where clause is updating only for employee with id =101. If we will remove where clause then it will update data of all employee who’s Id will match in “Emp” and “user” table.   

Update data without using Joins

In above example we have used JOINS but we can also do it without joins let’s check out how

UPDATE Emp, User
SET
 Emp.Age = User.Age,
 Emp.Salary = User.Salary
WHERE Emp.id = User.id
And Emp.id = 101

Apart from UPADTE, we can also INSERT data into our table by using the SELECT statement as given in the below sample query. One thing which is important is – we need to pass the same number of columns in INSERT and SELECT statement as I have passed only three column names (cols, col2, col3) in below sample query. You can use n-number of columns, but number of columns and sequence must be same in INSERT and SELECT statement.

Insert data into table using SELECT statement

INSERT INTO EMP(col1, col2, col3)
SELECT col1, col2, col3 
FROM User  
WHERE id >100

In this article you have learned how to UPDATE using a SELECT statement in SQL Server. Additionally, we also learned this with or without using joins and also learn how to insert data into table using select query. For more such articles you can visit our SQL Section

We hope after reading this article you’ll able to use this in your daily development practices. For similar details you can also check out Microsoft Docs.

You may also like:

• SQL server Column Encryption using symmetric key.
• How is data physically stored in a SQL database
• SQL Server Interview Questions and Answers.
• Import One Database to another in SQL server.
• Generate database scripts in SQL server.
• Different ways to search SQL objects in database.
• SQL CASE statement
• SQL IIF function
• QUOTENAME function in SQL
• How to Take SQL Server Database Offline/Online
• How to use SQL ORDER BY in Your Queries
• SQL Paging using OFFSET and FETCH