PostgreSQL provides an UPDATE statement that is used along with the SET clause to update any particular table record. UPDATE statement uses the WHERE clause to specify a condition for updating the specific table records. All the rows that satisfy the given criteria will be updated with the specified value in such a case. If you didn’t specify a WHERE clause in the UPDATE query, then the entire table will be modified with the specified value.

This write-up will teach you how to update several records using a single UPDATE statement. So, let’s begin!

How to Update Multiple Rows in Postgres?

The below-mentioned syntax is used to update the table’s record with a new value:

UPDATE tbl_name
SET column_1 = value_1, column_2 = value_2, …, column_n = value_n
WHERE criteria;

Using the above syntax, you can modify one or more than one column.

Example 1: Updating Multiple Rows With the Same Value

We have created a table named “emp_info” that contains the following records:

SELECT * FROM emp_info;

We will update all employees whose emp_id is less than 4 with "Seth" by executing the below statement:

UPDATE emp_info 
SET emp_name = 'Seth'
WHERE emp_id < 4;

The output shows that three records have been updated, you can verify updated records using the following statement:

The output authenticates that multiple records have been updated with the same value.

Example 2: Updating All Rows With the Same Value

We can modify all table rows at once by skipping the WHERE clause from the UPDATE statement:

UPDATE emp_info 
SET emp_name = 'Seth';

The above snippet will update all the rows of the emp_name column with the same value, i.e., “Seth”:

The output snippet shows that six records have been modified. Let’s verify the updated rows using the SELECT command:

SELECT * FROM emp_info;

The output authenticates that skipping the WHERE clause updated the whole column with the same value.

Example 3: Updating Multiple Rows With Different Values

We must execute the update query with the semi-colon-separated syntax to modify multiple rows with different values:

UPDATE emp_info SET emp_id = 10, emp_name = 'joseph' WHERE emp_id = 3;
UPDATE emp_info SET emp_id = 12, emp_name = 'joe' WHERE emp_id = 1;
UPDATE emp_info SET emp_id = 13, emp_name = 'Ambrose' WHERE emp_id = 2;

Let’s verify the updated records using the SELECT statement:

SELECT * FROM emp_info;

The output verified that multiple records had been updated in the emp_info table.

That’s it from this Postgres blog!

Conclusion

In PostgreSQL, the UPDATE query must be executed with the semi-colon-separated syntax to modify multiple rows with different values. In Postgres, the UPDATE statement is used along with the SET clause to update any particular table record. In the WHERE clause, users can specify a condition based on which the table will be updated. All the rows that satisfy the given criteria will be updated with the specified value in such a case. Skipping the WHERE clause will modify the entire table with the specified value. This write-up explained how to update multiple table rows with the same or different values using the Postgres UPDATE query.