Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL UPDATE

The `UPDATE` statement in PostgreSQL is used to modify existing records in a table. It allows you to change the values of one or more columns for rows that meet specified conditions.

Usage

The `UPDATE` statement is employed when you need to change data within a table without deleting and re-inserting rows. It requires specifying the table, the columns to be updated, and the new values, often accompanied by a `WHERE` clause to limit the scope.


UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition];

In this syntax, `UPDATE table_name` specifies the table to modify, and `SET` assigns new values to the specified columns, optionally filtered by a `WHERE` condition.

Examples

1. Basic Update


UPDATE employees
SET salary = 50000
WHERE employee_id = 123;

This example updates the `salary` column to 50,000 for the employee with an `employee_id` of 123.

2. Update Multiple Columns


UPDATE products
SET price = price * 1.1, stock = stock - 5
WHERE product_id = 456;

Here, the `price` is increased by 10%, and `stock` is reduced by 5 for the product with `product_id` 456.

3. Conditional Update with Subquery


UPDATE customers
SET status = 'inactive'
WHERE last_order_date < (SELECT NOW() - INTERVAL '1 year');

This example sets the `status` to 'inactive' for customers who haven't placed an order in over a year, utilizing a subquery for the date condition.

4. Using RETURNING Clause


UPDATE employees
SET salary = 50000
WHERE employee_id = 123
RETURNING employee_id, salary;

This example not only updates the `salary` but also returns the `employee_id` and the updated `salary` for verification purposes.

Tips and Best Practices

  • Use WHERE clauses wisely. Always use a `WHERE` clause to target specific rows, avoiding unintended updates to every row in the table.
  • Backup data before updating. Back up your data before performing large updates to prevent data loss.
  • Test updates with transactions. Use transactions to test updates, allowing you to roll back changes if they don't meet your expectations.
  • Monitor performance. Regularly check for performance impacts, especially when updating large datasets or using complex conditions.
  • Be aware of concurrency issues. When updating rows in a multi-user environment, be mindful of potential locking issues. Consider using appropriate isolation levels to manage concurrent transactions effectively.