w3resource

PostgreSQL Update Table: Update the salary of an employee with a new value for some specific employees in HR database


6. Write a SQL statement to change the salary of an employee to 8000 whose ID is 105, if the existing salary is less than 5000.

Sample table: employees


Sample Solution:

Code:

-- This SQL statement updates the 'SALARY' column in the 'employees' table for an employee with employee_id=105 
-- if their current salary is less than 5000. It sets their salary to 8000.

UPDATE employees 
SET SALARY = 8000 
WHERE employee_id = 105 
AND salary < 5000;

Explanation:

  • The UPDATE statement is used to modify existing records in a table.
  • employees is the name of the table being updated.
  • SET SALARY = 8000 specifies that the value of the 'SALARY' column should be set to 8000 for the rows that meet the specified condition.
  • WHERE employee_id = 105 AND salary < 5000 restricts the update operation to only the row where the value of the 'employee_id' column is 105 and the value of the 'salary' column is less than 5000. This ensures that only the employee with employee_id=105 and a salary less than 5000 will have their salary updated to 8000.

Output:

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

postgres=# SELECT * FROM  employees 
WHERE  SALARY = 8000 
AND employee_id = 105;

 employee_id | first_name | last_name |  email  | phone_number | hire_date  | job_id  | salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------+--------------+------------+---------+---------+----------------+------------+---------------
         105 | David      | Austin    | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 8000.00 |           0.00 |        103 |            60
(1 row)

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 those employees who belongs to the 'Accounting' department.
Next: Write a SQL statement to change the job ID of the employee which ID is 118 to SH_CLERK if the employee belongs to a department which ID is 30 and the existing job ID does not start with SH.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.