PostgreSQL Update Join + Examples

In this PostgreSQL tutorial, we are going to learn about Postgresql update join. Here we will learn how to update join in PostgreSQL, and we will also cover the following list of topics.

• Postgresql update join
• Postgresql update join subquery
• Postgresql update join with alias
• Postgresql update join group by
• Postgresql update join same table
• Postgresql update join slow
• Postgresql update left join
• Postgresql update join example
• Postgresql update self join

Postgresql update join

In Postgresql, the UPDATE statement is usually used to change the value of a column in a table. We will explain the conditions that tell which rows get updated by using a WHERE clause. If we modify the WHERE clause from our UPDATE statement, the values for the column get changed for each row in the table.

The JOIN in Postgresql is basically used to match and combine records from various tables. If an UPDATE statement is used in JOIN then we are able to show a cross-table update which means that a record in a table is changed if a value matched records from the other table.

To join another table in the UPDATE statement, we will describe the joined table in the FROM clause and provide the join condition in the WHERE clause. The FROM clause must appear right after the SET clause. For each row of table1, the UPDATE statement will check every row of table2.

We have a detailed discussion on the topic “Postgres update with join“.

Postgresql update join subquery

The UPDATE statement in Postgresql is used to change the value of a column in a table. WHERE clause is used in which we can describe the conditions that dictate which rows get updated. If we discard the WHERE clause from our UPDATE statement, the values for the column will be changed for every row in the table.

The JOIN operator in Postgresql is used to match and combine records from different tables. If we use a JOIN in an UPDATE statement, we can perform a cross-table update which means that a record in a table will be changed if a value matched records from the second table.

Postgresql update joins subquery basically means to update the join statement. We have taken the examples of the tables which we created earlier i.e. Movies and movie_genre. Let’s check the query for this.

With subquery AS (
SELECT genre_id, movie_genre
FROM Movie_genres
)
UPDATE Movies
SET movie_genre = subquery.movie_genre
FROM Movies AS m
LEFT JOIN subquery on m.genre_id = subquery.genre_id
WHERE Movies.movie_id = m.movie_id;

Here is the output for the above query.

Read: PostgreSQL Loop Examples

Postgresql update self join

Self-join in PostgreSQL is generally a query to merge the same tables with different aliases. For performing a self-join, we have to reference the same table two times but with various aliases to discard confusion between tables.

The self-join is checked as a joining of two copies of the same table. We have to set the comparison and eliminate the condition of the same rows. The syntax for self-join is the same as normal joins. The only difference is that this time the Table2 table will also be the same as Table1.

Self-join is implemented through INNER, LEFT, RIGHT, FULL joins. Basically, we have to update the self-join statement. We have two tables Movies and Movies_genre. Let’s check the syntax for self-join and we will use RIGHT join for implementation.

UPDATE table1
SET table1.column1 = new_value
FROM table2
WHERE table1.column2 = table2.column2;

Let’s check the query now.

UPDATE Movie_Genres
SET movie_genre = m.movie_genre
FROM Movie_Genres AS g
RIGHT JOIN Movies AS m on g.genre_id = m.genre_id
WHERE Movie_Genres.movie_id = g.movie_id;

Let’s check the output now.

In the below output we have updated the values of the movie_genre column in the table movie_genres using self join in which we have used right join using an UPDATE statement. Let’s check the output after updation.

Read: Postgresql Sum – How to use

Postgresql update left join

The LEFT JOIN in Postgresql combines two tables and gets rows based on a condition, which is the same in both tables and the unmatched rows will also be accessible from the table written before the JOIN clause.

In PostgreSQL LEFT JOIN, the second table is dependent on the first table and all tables on which the first table depends, and also the first table depends on all tables that are used in the LEFT JOIN condition except the second table.

The LEFT JOIN condition is used to decide how to retrieve rows from the second table. Now with the help of the tables which we have created Movie and Movie_genres, we will implement the query of LEFT Join. Now let’s check the query for left join.

UPDATE Movies
SET movie_genre = g.movie_genre
FROM Movies AS m
LEFT JOIN Movie_genres AS g on m.genre_id = g.genre_id
WHERE Movies.movie_id = m.movie_id;

Let’s check the output before updation.

In the below output we have updated the values of the column movie_genre in the Movie table with the help of the left join query using the UPDATE statement. Let’s check the output after updation.

Read: Postgresql while loop

Postgresql update join group by

The GROUP BY clause in Postgresql will divide the rows returned from the SELECT statement into groups. We can use an aggregate function to calculate the sum of items in each group, such as SUM(), or COUNT() to determine the number of things in each group.

The below statement describes the syntax of the GROUP BY clause. Firstly, we will choose those columns that we want to group e.g., column1 and column2, and column that we want to apply an aggregate function (column3).

Secondly, list the columns that we want to group in the GROUP BY clause. The below statement describes the syntax of the GROUP BY clause. The UPDATE statement does not support GROUP BY in Postgresql.

If we are trying to update the table1 with the corresponding row from table2, we want to use the WHERE clause. We have a table named weather and here is the update query for it.

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
  WHERE city = 'Austin' AND date = '2021-11-01';

Let’s check the output now.

In the above output, we have updated the values of the temp_lo and temp_hi columns.

Read: Postgresql REGEXP_REPLACE Function

Postgresql update join slow

Query performance in Postgresql is generally affected by many things. Some of these can be manipulated by the user, while others are basic to the underlying design of the system. This topic provides some suggestions about understanding and controlling PostgreSQL performance.

PostgreSQL comes up with a query plan for every query it is given. We determine the right plan to match the query structure and the properties of the data is definitely critical for better performance. We generally use the EXPLAIN command to see what query plan the system creates for any query.

EXPLAIN SELECT * FROM best_motor_cycle;
EXPLAIN SELECT * FROM print;

Let’s check the output now.

Rows output is quite difficult since it is not the number of rows processed or scanned by the query which is usually less, reflecting the estimated selectivity of any WHERE-clause conditions that are being applied at this code. Generally, the top-level row counts an approximate number of rows really returned, updated, or deleted by the query.

We will find out that best_motorcycle has 233 disk pages and with 120(best_motorcycle) and 2260(print) rows. So the cost is estimated at 233-page reads, defined as costing 1.0 apiece, plus 2260 * cpu_tuplecost which is currently 0.01.

Now we will update the statement with EXPLAIN command. We have tables named students, course and teachers. We will update by using EXPLAIN command. Let’s check the query now.

EXPLAIN UPDATE students
SET course_name = course.course_name
FROM course, teachers
WHERE students.course_id = course.course_id
AND course.teacher_id = teachers.teacher_id;

Now let’s check the output.

Read: Postgresql Having Clause

Postgresql update join with alias

Alias in PostgreSQL is a temporary name given to a table. Suppose, table it’s getting parsed as a table named t1. The name of a column within the table is named such as table 1. The column name is qualified with a subfield name or array subscript if needed.

We should not include the table’s name in the specification of a target column like UPDATE table_name SET table_name.col = 1 is false. Table aliases briefly assign tables new names through the execution of a query. In this syntax, the table_name is given an alias as alias_name.

Same to column aliases, the AS keyword is optional. It means that we discard the AS keyword like this. If we use table aliases for the long table name to make more readable queries.

We enable a column name with a long table name, we can use a table alias to save some typing and make our query more readable. Let’s check the below query.

CREATE TABLE "table" AS
SELECT x AS column, x AS column2
FROM generate_series(1,12345) AS t(x);
UPDATE "table" t1 SET "column"=0 WHERE t1.column2=1234;

Let’s check the output for the above query.

Read: PostgreSQL add primary key

Postgresql update join example

In this topic, we will learn about various types of PostgreSQL joins such as inner join, left join, right join, and full outer join. Generally, PostgreSQL join is used to join columns from one which is self-join or multiple tables based on the values of the common columns among related tables.

The familiar columns are basically the primary key columns of the first table and foreign key columns of the second table. Postgresql has inner join left join, right join, full outer join, cross join, natural joins and it has a special type of join called self-join.

We have a detailed discussion on this topic. Here is the link for that.

Read: PostgreSQL Min With Examples

Postgresql update join same table

We will update the records which are stored in the table in various ways. PostgreSQL will provide UPDATE JOIN to do the same thing. In most cases, we will update one table’s records based on another table’s records.

To join other tables in that statement, we will explain the PostgreSQL FROM clause with the joined table, along with we want to describe the PostgreSQL WHERE clause along with a JOIN condition. We have to attach the SET clause and have to explain the PostgreSQL FROM clause immediately after it.

Let’s have a look at the example before updating and after updating. Also will understand the queries for it.

UPDATE studt
SET studt_total_marks = studt_total_marks + 40
FROM
deptt
WHERE
studt.deptt_id = deptt.deptt_id AND deptt.deptt_id <> 3;

Let’s check the output before updating.

Let’s check the query after updating the value.

You may also like to read the following articles on PostgreSQL.

• Postgresql Add Foreign Key
• PostgreSQL Update + Examples
• Postgresql date comparison
• Postgresql date to string
• PostgreSQL Rename Column

In this PostgreSQL tutorial, we have learned about PostgreSQL Update Join. Here we have learned how to update join queries available in PostgreSQL, and we have also covered the following list of topics.

• Postgresql update join
• Postgresql update join subquery
• Postgresql update join with alias
• Postgresql update join group by
• Postgresql update join same table
• Postgresql update join slow
• Postgresql update left join
• Postgresql update join example
• Postgresql update self join