• Jagan Mohan Reddy Rajidi

SQL query to update from join

Updated: 4 days ago

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,




6 views0 comments

Recent Posts

See All

When you create any new object(table, stored procedure, view,...etc.) in database then same object name will be stored in system table Sysobjects. Following query returns all the object names along wi

Sometimes you get a scenario where you know the column name but don't remember which table it belongs to. Following query will search for a given column name in database schema and returns all objects