How to Use the Postgres to Update From Another Table
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