In PostgreSQL, we can manipulate the database data in a customized way using the DML clauses. Using these clauses, we can SELECT, INSERT, DELETE, and UPDATE data. The data can be updated in a way that we can set the value of any row to a new value and we can also update the data of one table according to the data of another table. This is the function or UPDATE and UPDATE FROM statements respectively.

This write-up will draw a comparison between the UPDATE and the UPDATE FROM statement in PostgreSQL.

Postgres UPDATE Statement

The UPDATE statement works in a simple way in that it updates the pre-existing value in the table with a new value. Let’s update the name of a student as “Jack” from the table “attendance_list”.

The query can be written as:

UPDATE attendance_list
 SET st_name = 'Jack'
 WHERE st_id = 7
 SELECT * FROM attendance_list;

The above query will update the name of a student having ID 7. The output is:

We can see that the name of the student has been updated from “John” to “Jack”.

Postgres UPDATE FROM Statement

The UPDATE FROM statement has the extended functionality as of the UPDATE statement. The UPDATE FROM statement is also used to update the table values. In addition to that, the UPDATE FROM statement is used to update the value of one table according to the other.

For example, if we want to update the value in the table “attendance_list” to get the scores of each candidate, from the other table named “test_scores” concatenated with their names.

The table “attendance_list” is given as:

The table named “test_scores” containing the data of candidates and scores is given as:

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;

We will get the updated “attendance_list” table having the st_name column with updated value as the name of student/candidate concatenated with the scores through the “got” string. The output is illustrated below:

So this is how the UPDATE FROM statement functions.

PostgreSQL UPDATE Vs. UPDATE...FROM - What's the Difference

The UPDATE and UPDATE FROM statements are used to update the data in the Postgres table. Both these statements update the table’s data, but they work differently. The difference between the UPDATE and UPDATE FROM statements is only that the UPDATE FROM statement extends the functionality of the UPDATE statement. In the UPDATE FROM statement, we need to have two tables; the one that is to be updated and the one according to which the value has to be updated.

Conclusion

The UPDATE FROM provides the extended functionality of the UPDATE statement. The UPDATE statement updates the table's data with the new values provided in the query. While the UPDATE FROM statement updates a table's data in accordance with the values of the other table. This is the difference between both statements. This blog illustrated the difference between the UPDATE and the UPDATE FROM statements with practical examples.