View the Exhibit and examine the structures of the employees and departments tables.
You want to update the employees table as follows:
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department_id corresponding to London
(location_id 2100).
-Set the employees’ salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees’ commission in iocation_id 2100 to 1.5 times the average commission of their
department.
You issue the following command: 
What is the outcome?
A.
It executes successfully and gives the correct result.
B.
It executes successfully but does not give the correct result.
C.
It generates an error because a subquery cannot have a join condition in an update statement.
D.
It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified
together in an update statement.
Explanation:
Whats the expected result then?
Got it
I think WHERE DEPARTMENTS.LOCATION_ID IN (2900, 2700, 2100)clause is wrong. It will be WHERE DEPARTMENTS.LOCATION_ID IN (2100).
why b? please explain it
Thanks to the bottom WHERE statement with subquery, 1.1 times the average salary and 1.5 times the average commission are set to locations 2900 and 2700, instead of 2100.
Using the HR schema I copied the employees and departments tables, manually adjusted them to match the tables in this example, ran the code and was left with:
0 rows updated.
I then tried Sayed’s idea and got the same result.
I’d love if a kind soul could explain this one!
I think only two consecutive UPDATEs in the exact order below would be a solution;
(When testing in HR, you might need to disable UPDATE_JOB_ID trigger):
UPDATE employees
SET (salary,commission_pct) =
(SELECT 1.1 * AVG(salary), 1.5 * AVG(commission_pct)
FROM employees, departments
WHERE departments.location_id IN (2900,2700,2100))
WHERE department_id IN
(SELECT department_id FROM departments
WHERE location_id IN (2100));
UPDATE employees
SET department_id =
(SELECT department_id
FROM departments
WHERE LOCATION_id = 2100)
WHERE department_id IN
(SELECT department_id FROM departments
WHERE location_id IN (2900,2700));
So, I was looking over this one again and while looking at the Oracle docs….voila, this exact question is right there in their examples:
http://docs.oracle.com/database/121/SQLRF/statements_10008.htm#SQLRF01708
UPDATE employees a
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = ‘2100’),
(salary, commission_pct) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)
FROM employees b
WHERE a.department_id = b.department_id)
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700);
The preceding UPDATE statement performs the following operations:
Updates only those employees who work in Geneva or Munich (locations 2900 and 2700)
Sets department_id for these employees to the department_id corresponding to Bombay (location_id 2100)
Sets each employee’s salary to 1.1 times the average salary of their department
Sets each employee’s commission to 1.5 times the average commission of their department
I think there is a join part missing from the following part of the query
(salary,commission_pct) =
(SELECT 1.1 * AVG(salary), 1.5 * AVG(commission_pct)
FROM employees, departments
WHERE departments.location_id IN (2900,2700,2100))
There is nothing that joins employees and departments. Unlike the correlated
subquery in Oracle documentation that Brian presented.
that is why the above query produces no result
The main problem of this update is these requests:
-Set the employees’ salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees’ commission in iocation_id 2100 to 1.5 times the average commission of their
The target of the update are the employees on the locations 2900 and 2700(specified in the main WHERE). I agree with Dames, I see no way of updating differents columns for differents row sets with a single command.
Also Val above is almost right, it’s missing a join clause in the subquery for the salary and commission, although it wouldn’t prevent its execution; it would only produce incorrect values for the set.