PostgreSQL Update Table: Update the values of one or more column of a table against more than one criteria

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

Sample table: employees

Sample Solution:

Code:

-- This SQL statement updates the 'email' column in the 'employees' table for employees 
-- belonging to the department with department_id=80 and having a commission percentage less than 0.20.
-- It sets the 'email' column to 'not available' for matching rows.

UPDATE employees 
SET email='not available'
WHERE department_id=80 
AND commission_pct<0.20;

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 conditions.
• WHERE department_id=80 AND commission_pct<0.20 restricts the update operation to only those rows where the value of the 'department_id' column is 80 and the value of the 'commission_pct' column is less than 0.20. This ensures that only employees belonging to the department with department_id=80 and having a commission percentage less than 0.20 will have their email addresses updated to 'not available'.

Output:

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

postgres=# SELECT *
postgres-# FROM employees
postgres-# WHERE department_id=80
postgres-# AND  email='not available'
postgres-# AND commission_pct<.20;
 employee_id | first_name | last_name |     email     |    phone_number    | hire_date  | job_id | salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------------+------------+--------+---------+----------------+------------+---------------
         155 | Oliver     | Tuvault   | not available | 011.44.1344.486508 | 1987-08-11 | SA_REP | 7000.00 |           0.15 |        145 |            80
         163 | Danielle   | Greene    | not available | 011.44.1346.229268 | 1987-08-19 | SA_REP | 9500.00 |           0.15 |        147 |            80
         164 | Mattea     | Marvins   | not available | 011.44.1346.329268 | 1987-08-20 | SA_REP | 7200.00 |           0.10 |        147 |            80
         165 | David      | Lee       | not available | 011.44.1346.529268 | 1987-08-21 | SA_REP | 6800.00 |           0.10 |        147 |            80
         166 | Sundar     | Ande      | not available | 011.44.1346.629268 | 1987-08-22 | SA_REP | 6400.00 |           0.10 |        147 |            80
         167 | Amit       | Banda     | not available | 011.44.1346.729268 | 1987-08-23 | SA_REP | 6200.00 |           0.10 |        147 |            80
         171 | William    | Smith     | not available | 011.44.1343.629268 | 1987-08-27 | SA_REP | 7400.00 |           0.15 |        148 |            80
         172 | Elizabeth  | Bates     | not available | 011.44.1343.529268 | 1987-08-28 | SA_REP | 7300.00 |           0.15 |        148 |            80
         173 | Sundita    | Kumar     | not available | 011.44.1343.329268 | 1987-08-29 | SA_REP | 6100.00 |           0.10 |        148 |            80
         179 | Charles    | Johnson   | not available | 011.44.1644.429262 | 1987-09-04 | SA_REP | 6200.00 |           0.10 |        149 |            80
(10 rows)

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

Previous: Write a SQL statement to change the email and commission_pct column of the employees table with 'not available' and 0.10 for those employees whose department_id is 110.
Next: 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.