SQL query to update from join

This article will show you simple technique about how to update a table from the result of multiple tables joining query.

Create 3 tables to manage employee hobbies.

create table emp(empId int,name varchar(20))
insert into emp values(1,'Krishna')
insert into emp values (2,'Chris')
insert into emp values(3,'Steve')

create table hobbies(hobbyId int,hobby varchar(30))
insert into hobbies values (1,'Painting')
insert into hobbies values (2,'Singing')
insert into hobbies values (3,'Painting')
insert into hobbies values (4,'Swimming')

create table EmpHobbies(empId int,hobbyId int,empname varchar(20),hobby varchar(20))
insert into EmpHobbies(empId,hobbyId) values(1,2)
insert into EmpHobbies(empId,hobbyId) values(2,4)
insert into EmpHobbies(empId,hobbyId) values(3,1)

Follow below 3 steps that give you simple technique to update a table from joining multiple tables

Step1

Write a select query of emp,hobbies and EmpHobbies to get employees with hobby details.

select *
from EmpHobbies EmpHobbies
inner join emp emp on EmpHobbies.empId= emp.empId
inner join hobbies hobbies on EmpHobbies.hobbyId= hobbies.hobbyId

Step2

In the select query, display columns to be updated and destination columns for values.

select EmpHobbies.empname,emp.name,EmpHobbies.hobby,hobbies.hobby 
from EmpHobbies EmpHobbies
inner join emp emp on EmpHobbies.empId= emp.empId
inner join hobbies hobbies on EmpHobbies.hobbyId= hobbies.hobbyId

Step3

This is the final step for update query. Here do not change anything from the place "FROM" command. You need to change the select statement with update statement that is just replace "select EmpHobbies.empname,emp.name,EmpHobbies.hobby,hobbies.hobby" with "update EmpHobbies set EmpHobbies.empname=emp.name,EmpHobbies.hobby=hobbies.hobby"

update EmpHobbies set  EmpHobbies.empname=emp.name,EmpHobbies.hobby=hobbies.hobby 
from EmpHobbies EmpHobbies
inner join emp emp on EmpHobbies.empId= emp.empId
inner join hobbies hobbies on EmpHobbies.hobbyId= hobbies.hobbyId

Result of table EmpHobbies after executing after above query,