Using a Subquery to Update

The search condition of a WHERE clause can be a subquery. Suppose you want to change the manager of the department that Katherine Young current manages. One way to do this is to first determine Katherine Young’s department number (don’t do this yet):

SELECT dept_no FROM Employee 
WHERE full_name = 'Young, Katherine'

This query returns “623” as the department. Then, using 623 as the search condition in an UPDATE, you change the department with the following statement:

UPDATE Department
SET mngr_no = 107
WHERE dept_no = '623'

A more efficient way to perform the update is to combine the two previous statements using a subquery. A subquery is one in which a SELECT clause is used within the WHERE clause to determine which rows to update.

Updating Department Using a Subquery

1. So that you can see the results of this exercise, begin by entering the following query to show you the manager number of Katherine’s department before you make the update:

SELECT mngr_no FROM department WHERE dept_no = '623'

This returns 15. (If you select first_name and last_name from the Employee table where emp_no equals 15, you will see that the manager of department 623 is Katherine herself.)

2. Enter and execute the following UPDATE statement with a subquery to simultaneously find out Katherine’s department number and assign a new manager number to that department:

UPDATE Department
<SET mngr_no = 107
<WHERE dept_no = (SELECT dept_no FROM Employee
<WHERE full_name = 'Young, Katherine')

The rows returned by the SELECT statement within the parentheses are the rows that the UPDATE statement acts on.

3. Now run the query in step 1 again to see the change. The manager of department 623 is manager number 107, rather than 15.

4. This isn’t a change we want to keep, so enter and execute the following statement to reinstate Katherine Young as manager of department 623.

UPDATE Department SET mngr_no = 15 WHERE dept_no = '623'

Advance To:

Deleting Data