How to Update Query In PostgreSQL

In this PostgreSQL tutorial, I will demonstrate to you how to update a query in PostgreSQL. Also, you will understand how to update single or multiple rows at a time.

Then I will show how to get updated or modified rows using the clause ((RETURNING *)).

How to Update Query In PostgreSQL

To modify the records in the table UPDATE query is used, it allows you to modify one or multiple records. In other words, If you want to update one or more than one record, you use the UPDATE query.

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 name 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 how to update a query in PostgreSQL.

How to Update Query In PostgreSQL with Single Row

You can update or modify the single or one 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 have to update the country for the customer ‘Emma’ from Canada to the USA, you can use the below command.

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

SELECT * FROM customers;

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.

So this is how you can update the one record of the table using the UPDATE query in PostgreSQL.

How to Update Query In PostgreSQL for Multiple Rows

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

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

If you see the table there are three customers Michael, Robert, and Emma with account status equal to inactive.

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

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

SELECT * FROM customers;

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

So this is how you can update multiple records using the PostgreSQL update query.

Read Also: How To Check Oracle Database Read Write Mode

How to Update Query In PostgreSQL Return Modified Rows

In this section, you will know how to get the modified rows after the updation of rows. In the previous examples, you updated the rows and view the modified rows using the simple ‘SELECT’ statement.

There is a small alteration to the syntax of the UPDATE query to get 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 to ‘inactive’ who belong to country USA.

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

The output shows the account status of the employee who resides in country USA is 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’).

Conclusion

In this PostgreSQL tutorial, you have learned how to update queries 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