Combine Tables in Postgres with Update to Join Columns
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
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 the
PostgreSQL database cluster is required when using Postgres to update with the join syntax. Execute the
systemctl status postgresqlcommand to verify if PostgreSQL is installed.
Possess a basic knowledge of database management or SQL commands and be able to use the
psqlto console apply the syntax to the database. Execute the
psql -Vcommand 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
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
WHERE SQL clauses to join, or combine with the
UPDATE operation, a column of records from both tables. Following is an example:
SET name = institute.inst_name
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:
The above SQL statement should now return the following results of student IDs from both tables:
student_id | name | email
1 | BSED | email@example.com
2 | BSIT | firstname.lastname@example.org
3 | Engineering | email@example.com
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