Update one table with data from another table :- Some time we need to update more than one column in a SQL table with values from column in another table.So we can achieve this using join. Please refer the below example:-

Table Structure & Values In below example I have created two tables, TempStudent and TempBooks, StudentId is the primary key in TempStudent table.

CREATE TABLE #TempStudent 
( 
StudentID int NOT NULL PRIMARY KEY,
FirstName varchar(50), 
LastName varchar(50), 
Email varchar(50)
);

INSERT INTO #TempStudent VALUES  (1,'Rahul', 'Joshi','xyz@gmail.com')
INSERT INTO #TempStudent VALUES (2,'Amit', 'Joshi','abc@gmail.com')
INSERT INTO #TempStudent VALUES (3,'Ravi', 'Sharma','uyt@gmail.com')
INSERT INTO #TempStudent VALUES (4,'Ajit', 'Rana','yjt@gmail.com')

select * from #TempStudent

Table 2:- TempBook

CREATE TABLE #TempBook 
( 
BookID int NOT NULL PRIMARY KEY,
StudentId int REFERENCES #TempStudent(Id),
Title varchar(50), 
AuthorFirstName varchar(50),
AuthorLastName varchar(50)
);

INSERT INTO #TempBook VALUES  (1,1,'Let Us C', 'yashwant',' kanetkar')
INSERT INTO #TempBook VALUES (2,1,'Effective C++', 'Scott',' Meyers')
INSERT INTO #TempBook VALUES (3,2,'The C++ Cookbook', 'Jonathan',' Turkanis')
INSERT INTO #TempBook VALUES (4,4,'Design Patterns', 'Steve',' McConnell')

select * from #TempBook

Note: StudentId is Foreign key.

If we want a Single SQL Query to update the Column FirstName and LastName in Table #TempStudent from AuthorFirstNameand" and "AuthorLastName from Table #TempBook.

SQL Query:--

UPDATE #TempBook SET AuthorFirstName = S.FirstName, AuthorLastName = S.LastName FROM #TempBook AS B INNER JOIN #TempStudent  AS S ON B.StudentId = S.StudentId WHERE S.StudentId IN(1,2,4)

Result After Execution of SQL query:--