In this article, we will learn different methods that are used to the update data in a table with the data of other tables. The UPDATE from SELECT query structure is the main technique for the performing these updates.

An UPDATE query is used to the change an existing row or rows in the database. UPDATE queries can change all the tables’ rows, or we can limit the update statement affects for a certain rows with the help of the WHERE clause. Mostly, we use the constant values to change the data, such as the following structures.

Preparing the sample data

With the help of the following query, we will create Employee and Department tables and populate them with some synthetic data. These two tables have a relationship through the EmpID column, meaning that, in these two tables, the EmpID column value represents the same person.

CREATE TABLE dbo.Employee
( [EmpID]       INT
  PRIMARY KEY IDENTITY(1, 1) NOT NULL, 
  [Name]     VARCHAR(100) NULL, 
  [Salary]   DECIMAL(18,2),
  [DeptName] [varchar](100) NULL,
  [Location] [varchar](100) NULL
)
 
GO
 
CREATE TABLE  Department(
  [DeptId] [int]  PRIMARY KEY IDENTITY(1,1) NOT NULL,
  [EmpID] [int] NULL,
  [Name] [varchar](100) NULL,
  [Location] [varchar](100) NULL)
 
GO

 INSERT INTO Employee
(Name, Salary )
VALUES
(N'Dipak', N'30000'),
(N'Parth', N'35000'),
( N'Sagar', N'40000'),
( N'Priyank', N'45000'),
( N'Rajesh', N'50000')
 
GO
INSERT INTO Department
(EmpID,Name, Location)
VALUES
(1, N'PHP', N'Surat'),
(2, N'.Net', N'Pune'),
(3, N'React', N'Mumbai'),
(4, N'DBA', N'Ahemdabad')
 
SELECT * FROM Employee
SELECT * FROM Department

UPDATE from SELECT: Join Method

In this method, the table to be the updated will be joined with the reference (secondary) table that contains new row values. So that, we can access the matched data of the reference table based on a the specified join type. Lastly, the columns to be updated can be matched with a referenced columns and the update process changes these column values.

In the following example, we will update the DeptName and Location columns data with the Name and Location columns data of the Department table.

UPDATE EMP
SET  
  EMP.DeptName=DEPT.Name,
  EMP.Location=DEPT.Location
FROM Employee EMP INNER JOIN Department DEPT ON EMP.EmpID=DEPT.EmpID

After the execution of the update from a select query the output of the Employee table will be as shown below;

Select * from Employee

Let’s try to understand the above code:

We typed the table name, which will be updated after the UPDATE statement. After the SET keyword, we specified the column names to be the updated, and also, we matched them with the a referenced table columns. After the FROM clause, we retyped the table name, which will be the updated. After the INNER JOIN clause, we specified the referenced table & joined it to the table to be the updated. In addition to this, we can specify a WHERE clause & filter any columns of the referenced or updated table. We can also rewrite the query by using aliases for a tables.

Please give your valuable feedback and if you have any questions or issues about this article, please let me know.

Also Check SQL While Loop