How to write update query in PostgreSQL

In this PostgreSQL tutorial, I will demonstrate to you how to update a query in PostgreSQL. Additionally, you will learn how to update single or multiple rows simultaneously. Then I will show how to get updated or modified rows using the clause ((RETURNING *)).

How to write update query in PostgreSQL

To modify the records in the table, an UPDATE query is used, which allows you to modify one or multiple records. In other words, if you want to update one or more records, you use the UPDATE query.

Syntax

The syntax is given below.

UPDATE tablename
SET column_name1 = value_1, column_name2 = value_2 .... column_nameN = value_N
WHERE condtion

• UPDATE: The UPDATE is the command to modify the table records.
• tablename: The name of the table whose records you want to update.
• SET column_name1 = value_1,… column_nameN = value_N: The names of columns with the new value you want to update or set.
• WHERE condition: The WHERE clause is used with the condition to modify or update specific records.

Using the above syntax, you can update or make changes to the column value of the existing records in PostgreSQL.

Let’s take some examples and understand in detail.

Example 1: For a Single Row

You can update or modify a single record using the PostgreSQL update query. Suppose you have a table called ‘customers’ with columns ‘first_name’, ‘last_name’, ‘country’, ‘account_status’, and ‘purchase_history’, which is shown below.

Now, you need to update the country for the customer ‘Emma’ from Canada to the USA. You can use the command below.

UPDATE customers
SET country = 'USA'
WHERE id = 2;

SELECT * FROM customers;

After executing the above query, I got the expected output as shown in the screenshot below.

The above query first finds the customers with id = 2 using the WHERE clause and modifies the country column value from Canada to the USA using (SET country = ‘USA’), which you can see in the above output.

This is how you can update a single record in a table using the UPDATE query in PostgreSQL.

Example 2: For Multiple Rows

Till now, you have learned how to update a single record at a time using a PostgreSQL UPDATE query. In simple words, when you have to modify multiple documents at the same time, you can also do that using the UPDATE query.

For example, I will use the same table as in the previous section. Now you want to update the account status of the customers from inactive to active.

As shown in the table, there are three customers, Michael, Robert, and Emma, with an account status of ‘inactive’.

Let’s update the account status using the code below.

UPDATE customers
SET account_status = 'active'
WHERE account_status = 'inactive';

SELECT * FROM customers;

If you examine the output, the account status of customers Michael, Robert, and Emma is updated to ‘active’ simultaneously. If the table contains thousands of records with account_status equal to ‘inactive’, then all the records will be updated using the above query.

This is how you can update multiple records using a PostgreSQL update query.

Read Also: How To Check Oracle Database Read-Write Mode

Example 3: Batch Updates with Subqueries

When dealing with large datasets, using subqueries can help improve performance:

UPDATE products
SET price = price * 1.10
WHERE category_id IN (SELECT id FROM categories WHERE name = 'Electronics');

This approach is especially useful for updating table rows based on conditions from other tables.

Example 4: Returning Modified Rows

In this section, you will learn how to get the modified rows after the update. In the previous examples, you updated the rows and viewed the modified rows using the simple ‘SELECT’ statement.

There is a minor adjustment to the syntax of the UPDATE query to retrieve updated rows.

UPDATE tablename
SET column_name1 = value_1, column_name2 = value_2 .... column_nameN = value_N
WHERE condtion

RETURNING *;

In the above syntax, after the WHERE clause, you have to specify the (RETURNING *) to get modified rows instantly as you perform the update operation on rows.

For example, you want to update the account status of employees from the USA to ‘inactive’.

UPDATE customers
SET account_status = 'inactive'
WHERE country = 'USA'
RETURNING *;

The output indicates that the account status of the employee residing in the USA has been updated to ‘inactive’. So, to know about the modified rows, you use the (RETURNING *) after the WHERE clause instead of using statements like (SELECT * FROM customers WHERE country = ‘USA’).

Best Practices

Here are some best practices I recommend:

1. Always use transactions for multiple updates to ensure data consistency.
2. Test your UPDATE queries on a development database first, especially for complex operations.
3. Use proper indexing on columns frequently used in WHERE clauses.
4. Consider the impact on other users when updating large tables in a production environment.
5. Use EXPLAIN ANALYZE to understand how your UPDATE queries perform.
6. Implement error handling to manage unexpected issues that may arise during updates.

Conclusion

In this PostgreSQL tutorial, you have learned how to write update query in PostgreSQL for single and multiple rows. Also learned about the clause (RETURNING *) to retrieve the modified rows.

You may also read:

• PostgreSQL Update Join + Examples
• Postgres Update Limit