w3resource

PostgreSQL Update Table: Change the value of more than one column with user defined content


2. Write a SQL statement to change the email and commission_pct column of the employees table with 'not available' and 0.10 for all employees.

Sample table: employees


Sample Solution:

Code:

-- This SQL statement updates the 'email' and 'commission_pct' columns in the 'employees' table,
-- setting the 'email' column to 'not available' and the 'commission_pct' column to 0.10 for all rows.

UPDATE employees SET email='not available', commission_pct=0.10;

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', commission_pct=0.10 specifies that the value of the 'email' column for all rows should be set to 'not available', and the value of the 'commission_pct' column should be set to 0.10. This effectively updates the email addresses to 'not available' and sets the commission percentage to 0.10 for all employees in the table.

Output:

See the result. Only two rows have been displayed.

postgres=# SELECT * FROM employees LIMIT 2;
 employee_id | first_name | last_name |     email     | phone_number | hire_date  |  job_id  | salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------+------------+----------+---------+----------------+------------+---------------
         128 | Steven     | Markle    | not available | 650.124.1434 | 1987-07-15 | ST_CLERK | 2200.00 |           0.10 |        120 |            50
         129 | Laura      | Bissot    | not available | 650.124.5234 | 1987-07-16 | ST_CLERK | 3300.00 |           0.10 |        121 |            50

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 the employees table with 'not available' for all employees.
Next: 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.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.