Friday, 25 August 2017

How to update with joining two or more table in SQL Server


Hello friend today we lean about update with joining two or more table.
First we create a database db_company
then we create two table 

then we insert some value in both table 
insert into tbl_Department(name) values('IT Department')
insert into tbl_Department(name) values('HR Department')
insert into tbl_Department(name) values('Marketing')
insert into tbl_employee(DPID, Name, Gender, Salary) values(1, 'JAMES', 'Male', 2000)
insert into tbl_employee(DPID, Name, Gender, Salary) values(1, 'PAUL', 'Male', 2000)
insert into tbl_employee(DPID, Name, Gender, Salary) values(2, 'SARA', 'Female', 2000)
insert into tbl_employee(DPID, Name, Gender, Salary) values(2, 'MARK', 'Male', 2000)
insert into tbl_employee(DPID, Name, Gender, Salary) values(3, 'TOMMY', 'Male', 2000)
insert into tbl_employee(DPID, Name, Gender, Salary) values(3, 'MARY', 'Feale', 2000)


all Employee have same salary 2000. Requirement is Update salary IT Department Salary 2000 to 2500   
update emp
set emp.salary = 2500
from tbl_Employee as emp
left join         tbl_Department as dep
on dep.id = emp.dpid
where dep.name = 'IT Department'


again we update salary Marketing Department Employee 2000 to 3000
update emp
set emp.salary = 3000
from tbl_Employee as emp
left join         tbl_Department as dep
on dep.id = emp.dpid
where dep.name = 'Marketing'

Thank You





Please Like , Share and Subscribe our Youtube channel