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.
Table of Contents
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