How to Use the Postgres to Update From Another Table

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

An SQL UPDATE statement is used to make changes to, or update, the data of one or more records in a table. When using the UPDATE statement, all of the rows in the table can be modified or just a subset may be updated using a condition. Once data has been added to a database, the SQL UPDATE command can be used to modify the column values in the rows of a table. This tutorial will explain how to use Postgres to update from another table.

Prerequisites

  • PostgreSQL must be properly installed and configured on the local system in order to use Postgres to update from another table.

  • A basic working knowledge of PostgreSQL is required to follow the instruction and execute the examples in this tutorial

PostgreSQL Update Statement Syntax

The basic syntax of the UPDATE Statement follows:

1
UPDATE TABLE_NAME SET column_name = new_value WHERE condition;

Following is an example of the update table syntax from another table:

1
2
3
UPDATE table_name1
SET column_name1 =  ( SELECT column_name2 FROM table_name2 WHERE condition )
WHERE condition;

PostgreSQL Update Statement Examples

The two tables in this section, named “Student” and “Scholar,” will be used to demonstrate this PostgreSQL UPDATE statement example.

The first is the Student table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 id  |       name       | grade | STATUS
-----+------------------+-------+--------
  32 | Allen James      |    89 | Passed
 371 | Jerry Lawrel     |    92 | Passed
  51 | Fred Smith       |    89 | Passed
 481 | Wayne Rosing     |    91 | Passed
 261 | Samuel Davis     |    92 | Passed
 621 | Philip Wilson    |    95 | Passed
 782 | Jay Leno         |    93 | Passed
 361 | MAX Heindel      |    97 | Passed
 891 | Gerry Starr      |    94 | Passed
 312 | Michelle Rogers  |    85 | Passed
 322 | Alfred Roosevelt |    91 | Passed
 536 | Tommy Jones      |    74 | Failed
 162 | Kelly Ripa       |    89 | Passed

Table second is the Scholar table:

1
2
3
4
5
6
7
8
9
 id  |     name      | grade
-----+---------------+-------
 621 | Philip Wilson |    95
 782 | Jay Leno      |    93
 361 | MAX Heindel   |    97
 891 | Gerry Starr   |    94
 481 | Wayne Rosing  |    91
 371 | Jerry Lawrel  |    92
 261 | Samuel Davis  |    92

Following is a basic example of how to update a table:

1
UPDATE student SET grade = '95' WHERE id = '361';

Note that the below table now has an updated grade of “95” for student id “361.”

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 id  |       name       | grade | STATUS
-----+------------------+-------+--------
  32 | Allen James      |    89 | Passed
 371 | Jerry Lawrel     |    92 | Passed
  51 | Fred Smith       |    89 | Passed
 481 | Wayne Rosing     |    91 | Passed
 261 | Samuel Davis     |    92 | Passed
 621 | Philip Wilson    |    95 | Passed
 782 | Jay Leno         |    93 | Passed
 891 | Gerry Starr      |    94 | Passed
 312 | Michelle Rogers  |    85 | Passed
 322 | Alfred Roosevelt |    91 | Passed
 536 | Tommy Jones      |    74 | Failed
 162 | Kelly Ripa       |    89 | Passed
 361 | MAX Heindel      |    95 | Passed

Now update the Scholar table with the following command:

1
2
3
UPDATE scholar
SET grade = ( SELECT grade FROM student WHERE id = '361' )
WHERE id = '361';

Now display the Scholar table:

1
2
3
4
5
6
7
8
9
10
SELECT * FROM scholar;
 id  |     name      | grade
-----+---------------+-------
 621 | Philip Wilson |    95
 782 | Jay Leno      |    93
 891 | Gerry Starr   |    94
 481 | Wayne Rosing  |    91
 361 | MAX Heindel   |    95
 371 | Jerry Lawrel  |    92
 261 | Samuel Davis  |    92

Next, add a column on the Scholar table and execute the following command to update multiple rows:

1
2
ALTER TABLE scholar
ADD COLUMN STATUS VARCHAR;

Finally, with a new column added, set the value from the Student table as follows:

1
2
3
UPDATE scholar SET STATUS = s.status
FROM student AS s
WHERE scholar.id = s.id;

Note that the table, shown below, has now been updated:

1
2
3
4
5
6
7
8
9
 id  |     name      | grade | STATUS
-----+---------------+-------+--------
 371 | Jerry Lawrel  |    92 | Passed
 481 | Wayne Rosing  |    91 | Passed
 261 | Samuel Davis  |    92 | Passed
 621 | Philip Wilson |    95 | Passed
 782 | Jay Leno      |    93 | Passed
 891 | Gerry Starr   |    94 | Passed
 361 | MAX Heindel   |    95 | Passed

Conclusion

This tutorial explained how to use Postgres to update from another table. The tutorial covered the basic PostgreSQL update-statement syntax and provided update statement examples including two table examples, The article also provided instructions on how to add a column and execute the command to update multiple rows as well as setting a new value for one table. Remember that when updating one table from another that the UPDATE statement can be used to modify all of the rows in the table or it can update just a subset using a specific condition.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.