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:

-- This SQL statement updates the 'email' column in the 'employees' table for employees 
-- belonging to the department identified by the department name 'Accounting'.
-- It sets the 'email' column to 'not available' for employees in the specified department.

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

Explanation:

• The UPDATE statement is used to modify existing records in a table.
• employees is the name of the table being updated.
• SET email='not available' specifies that the value of the 'email' column should be set to 'not available' for the rows that meet the specified condition.
• WHERE department_id=(SELECT department_id FROM departments WHERE department_name='Accounting') restricts the update operation to only those rows where the value of the 'department_id' column matches the department ID retrieved from the subquery. The subquery retrieves the department ID for the department named 'Accounting' from the 'departments' table. This ensures that only employees belonging to the 'Accounting' department will have their email addresses updated to 'not available'.

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%.