Combine Tables in Postgres with Update to Join Columns

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

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:

1
sudo su - postgres

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:

1
psql

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:

1
CREATE DATABASE dbtest;

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:

1
CREATE TABLE TABLE_NAME(COLUMN_NAME + DATA_TYPE + CONSTRAINTS [OPTION]);

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:

1
2
INSERT INTO TABLE_NAME(COLUMN1, COLUMN2,)
    VALUES(VAL1, VAL2);

An example follows:

1
INSERT INTO student(name, email) VALUES('Harith', 'har@gmail.com');

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:

1
2
3
4
UPDATE student
SET name = institute.inst_name
FROM institute
WHERE student.student_id = institute.inst_id;

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:

1
SELECT * FROM student;

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

1
2
3
4
5
6
7
dbtest=# SELECT * FROM student;
 student_id |    name     |       email
------------+-------------+--------------------
          1 | BSED        | har@gmail.com
          2 | BSIT        | jest@email.com
          3 | Engineering | ariane@yopmail.com
(3 ROWS)

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.

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.