PostgreSQL provides us with many statements using which we can write our queries to get the desired kind of data from the database tables. Using these statements, we can manipulate and also extract the database data. For example, we can fetch data using the SELECT query while the UPDATE query is used to manipulate the existing data into the newly provided data.

The content of this post covers the concepts of the UPDATE FROM statement. The UPDATE FROM statement is used to update table data according to some other table data. Let’s learn about this command in detail.

What Does the UPDATE ... FROM Statement Do in PostgreSQL?

The UPDATE FROM statement is used to update the data/row of any table according to the data of another table. For example, if we want the test scores of candidates to reflect on the attendance list as well, we can get the data from the “test_scores” table and update the “attendance_list” table.

The “attendance_list” table is given as follows:

The “test_scores” table looks like this:

Now if we want to add the scores of each candidate on the attendance list as well we can make use of the UPDATE FROM statement. The basic syntax for an UPDATE FROM statement can be written as:

UPDATE tab_name
 SET
  col_name1 = val1,
  col_name2 = val2,
  ...
 FROM second_table[, ...]
 WHERE cond
   [RETURNING expr];

In the above syntax:

● We first need to specify the name of the table which needs to be updated after the UPDATE statement.

● We need to set the values of the columns that will be updated.

● After the FROM statement, we will specify the name of the second table, the table according to which we want to update the values.

● We can write the condition after the WHERE clause that needs to be fulfilled for the updation to happen.

● At last, you can write the returning clause to get the resulting data of the query.

For the scenario we discussed above, the query can be written as:

UPDATE attendance_list a
 SET st_name = st_name || ' got ' || b.candidate_score
 FROM test_scores b
 WHERE a.st_name = b.candidate_name
 RETURNING st_id,st_name;

In this query:

● We have specified that the “attendance_list” has to be updated.

● The “st_name” column from “attendance_list” is set in a way that the student name will be concatenated with the “candidate_score” from the “test_scores” table.

● The concatenation is done by the “||” operator and the ‘ got ’ string.

● This is going to happen when the “st_name” from the attendance_list table is equal to the “candidate_name” from the test_scores table. This means that the score of each candidate is appended with its own name. This is the whole condition specified after the WHERE clause.

Now let’s see what this query returns.

In the above output, we can clearly see that the table “attendance_list” has been updated the same as the query was defined. The scores of each candidate are appended with the name using the ‘ got ’ string.

This is how we can update a table's data according to another table.

Conclusion

We can Update an existing table according to another table present in the database by making use of the UPDATE FROM clause. We need to specify the target table where the updation will take place after the UPDATE clause and the table according to which the update takes place is specified after FROM. This article demonstrated the working of the UPDATE FROM statement in PostgreSQL.