How to Use the Postgres to Update From Another Table

Written by Data Pilot

February 14, 2020

PostgreSQL

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

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

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

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

How to Use the Postgres to Update From Another Table

Introduction

Prerequisites

PostgreSQL Update Statement Syntax

PostgreSQL Update Statement Examples

Conclusion

Pilot the ObjectRocket Platform Free!

Keep in the know!

Services

Platform

Company

Resources

Support