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