Subquery in an UPDATE Statement to Change Values in Multiple Rows at Once

Using the UPDATE Statement with a Conditional Clause to Change Values in Multiple Rows at Once," show example UPDATE statements using WHERE clauses that determine a row's eligibility by using one of the comparison operators (=, >, <, <>, IS). The UPDATE statement will also let you use the results of a SELECT statement to specify which rows you want to UPDATE in the target table.

For example, suppose you want to reassign employees with a less than average number of sales to the training department. You could use the UPDATE statement:

UPDATE employees SET department = 'Training'
WHERE department <> 'Training'
AND sales < (SELECT AVG(sales)
FROM employees
WHERE department <> 'Training')

As another example, suppose you want to change the job title of all supervisors in charge of more than five employees to manager. You could use the UPDATE statement:

UPDATE employees SET job_title = 'Manager'
WHERE job_title = 'Supervisor'
AND 5 < (SELECT COUNT (*)
FROM employees WHERE reports_to = employee_id)

(In the current example, you could not omit the job_title = 'Supervisor' search condition because a vice president responsible for more than five employees would have his job title changed to manager.)

You can nest subqueries in the WHERE clause to any level, meaning that the SELECT statement in the UPDATE statement's WHERE clause can have a SELECT statement in its WHERE clause, which can have a SELECT statement in its WHERE clause, and so on.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: