PostgreSQL Update Table: Update the values of one or more column of a table using one or more tables and subqueries

5. Write a SQL statement to change the email column of the employees table with 'not available' for those employees who belongs to the 'Accounting' department.

Sample table: employees

Sample table: departments

Sample Solution:

Code:

UPDATE employees 
SET email='not available'
WHERE department_id=(
SELECT department_id 
FROM departments 
WHERE department_name='Accounting');

Output:

See the result. Only the effected rows have been displayed.

SELECT * FROM employees 
WHERE email='not available' AND department_id=
(SELECT department_id 
FROM dep 
WHERE department_name='Accounting');


 employee_id | first_name | last_name |     email     | phone_number | hire_date  |   job_id   |  salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------+------------+------------+----------+----------------+------------+---------------
         205 | Shelley    | Higgins   | not available | 515.123.8080 | 1987-09-30 | AC_MGR     | 12000.00 |           0.00 |        101 |           110
         206 | William    | Gietz     | not available | 515.123.8181 | 1987-10-01 | AC_ACCOUNT |  8300.00 |           0.00 |        205 |           110
(2 rows)

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to change the email column of employees table with 'not available' for those employees whose department_id is 80 and gets a commission is less than.20%.
Next: Write a SQL statement to change the email column of employees table with 'not available' for those employees whose department_id is 80 and gets a commission is less than.20%.