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 thepsql -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