Prev Next

PostgreSQL UPDATE

The UPDATE statement is used to modify existing rows in a PostgreSQL table. You can update one or multiple rows based on conditions specified in the WHERE clause.

Key Topics

1. Basic Syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...;

If you omit the WHERE clause, all rows will be updated with the new values.

2. Using WHERE Clause

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales';

This query gives a 10% salary increase to all employees in the 'Sales' department.

3. Using RETURNING

The RETURNING clause can be used to retrieve updated rows in one statement:

UPDATE employees
SET salary = salary + 500
WHERE emp_id = 101
RETURNING first_name, last_name, salary;

Code Explanation: This query updates the salary of the employee with emp_id = 101 and returns their name and new salary.

Best Practices

  • Always use a WHERE clause to limit updates unless you explicitly want to update all rows.
  • Use transactions or test updates in a staging environment to prevent accidental mass changes.
  • Use the RETURNING clause for immediate feedback on changes when needed.

Key Takeaways

  • The UPDATE statement modifies existing data in a table.
  • Specifying a WHERE condition is crucial to target specific rows.
  • The RETURNING clause can help validate changes without running a separate SELECT.
Prev Next