Hi Everyone,
I hope everyone is fine and learning more :). I need some advice on update statement and on its fine tuning. I am using Oracle 11gR2. I have three tables and joining those tables to update rows in one table. Please find below sample table and data scripts to explain concerned scenario.
create table employee (emp_id number, emp_name varchar2(50), manager_id number);
create table department (dept_id number, dept_name varchar2(50), emp_name varchar2(50), manager_level varchar2(20));
create table manager_lookup (manager_level_id number, manager_level varchar2(20));
insert into employee values (1, 'EmpA',3);
insert into employee values (2, 'EmpB',1);
insert into employee values (3, 'EmpC',1);
insert into employee values (4, 'EmpD',2);
insert into employee values (5, 'EmpE',1);
insert into employee values (6, 'EmpF',3);
insert into department values (1, 'DeptA','EmpD','Level3');
insert into department values (2, 'DeptB','EmpC','Level2');
insert into department values (3, 'DeptC','EmpA','Level1');
insert into department values (4, 'DeptD','EmpF','Level1');
insert into manager_lookup values (1, 'Level1');
insert into manager_lookup values (2, 'Level2');
insert into manager_lookup values (3, 'Level3');
commit;
I need to update manager_id in table employee from manager_level_id in table manager_lookup by joining employee with department on emp_name and department with manager_lookup on manager_level.
When I am running below update statement, it is updating all six rows in employee tables. Matching rows update to that manager_id while unmatched rows updating to null.
UPDATE employee a
SET manager_id =
(SELECT manager_level_id
FROM (SELECT mp.manager_level_id, dp.emp_name
FROM department dp, manager_lookup mp
WHERE dp.manager_level = mp.manager_level) b
WHERE a.emp_name = b.emp_name);
I add where clause for employee table which rows I want to update. so, I update script to below.
UPDATE employee a
SET manager_id =
(SELECT manager_level_id
FROM (SELECT mp.manager_level_id, dp.emp_name
FROM department dp, manager_lookup mp
WHERE dp.manager_level = mp.manager_level) b
WHERE a.emp_name = b.emp_name)
WHERE a.emp_name in (SELECT dp.emp_name
FROM department dp, manager_lookup mp
WHERE dp.manager_level = mp.manager_level);
Above update statement can get the work done but in real world problem, it is giving performance issues when dealing with big query involving more big tables in join clause.
Merge is also an another option but I am wondering how I can achieve this via update clause statement.
Can you please help on providing your wonderful suggestions or solutions? I will really appreciate all responses.
Thank you for going over the question.