Combine Tables in Postgres with Update to Join Columns

Introduction

This tutorial will explain how to use Postgres to update with the join syntax. The PostgreSQL UPDATE join syntax can be used to update the data of one table based on values from another separate table using the FROM and WHERE SQL clauses to combine the column values of the two individual tables. This type of update operation requires specifying the joined table in the FROM clause and also providing the join condition in theWHERE clause.

Prerequisites

• PostgreSQL database cluster is required when using Postgres to update with the join syntax. Execute the systemctl status postgresql command to verify if PostgreSQL is installed.

• Possess a basic knowledge of database management or SQL commands and be able to use the psql to console apply the syntax to the database. Execute the psql -V command to verify the current version of PostgreSQL

Enter the psql console

Access the interactive terminal in PostgreSQL, which is psql, by executing the following command:

This will cause the system to prompt for a root privilege password that will require the user’s password. Enter the password and then press the ENTER key. Now execute the following command to enter the terminal:

This will set up the user with a superuser role, in this case being postgres.

Create a database

A database is required in order to create a table, store a value inside that table and then update the table based on the values in a second table. Execute the following command to create the new database:

NOTE: In order to avoid syntax errors, a semicolon must always be used at the end of this command.

Now execute the \c command to connect to the newly created database.

Create a PostgreSQL table

The following syntax will be used to create a table in PostgreSQL:

For example, the table student can be updated using the value of the table institute:

Insert values inside the table

Execute the following command to insert the values into the table:

An example follows:

Use Postgres to update a table with a join SQL syntax

The table can be updated with the values from another table using a syntax made up of the FROM and WHERE SQL clauses to join, or combine with the UPDATE operation, a column of records from both tables. Following is an example:

Now use the following SELECT statement to display the updated student table where it will join the value of the column from another designated table:

The above SQL statement should now return the following results of student IDs from both tables:

Conclusion

This tutorial explained how to use Postgres to update with the join syntax. This update operation updated the data in one table using the values from another separate table. The article specifically covered how to access the interactive terminal in PostgreSQL and create a database for creating a table to store values in, how to create a PostgreSQL table and how to insert values inside the table. Finally the tutorial covered how to use Postgres to update a table with the join SQL syntax. When using Postgres to update a table with a join SQL syntax, bear in mind that the FROM clause must appear immediately after the SET clause. Also remember that a semicolon must always be used at the end of the command to avoid syntax errors.