Скачать или смотреть How to Update a PostgreSQL Table from Another Joined Table

Learn how to perform a PostgreSQL update on a table using data from another table with a many-to-many relationship, ensuring clarity with step-by-step solutions.
---
This video is based on the question https://stackoverflow.com/q/63477695/ asked by the user 'Albert' ( https://stackoverflow.com/u/5862455/ ) and on the answer https://stackoverflow.com/a/63477744/ provided by the user 'GMB' ( https://stackoverflow.com/u/10676716/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: PostgreSQL update a table from another one joined as many to many

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Update a PostgreSQL Table from Another Joined Table

When working with databases, we may often face challenges while merging data across related tables. One such scenario involves updating records in a PostgreSQL table based on values from another table, especially when dealing with a many-to-many relationship.

In this post, we will unravel a specific problem: updating the profile_pic of users in a user table with the name property taken from an image table, utilizing a bridge table, user_images_image, that connects them.

Understanding the Problem

Imagine you have the following three tables:

The user table, which stores user information.

The image table, which contains various image data.

The user_images_image bridge table that captures the many-to-many relationship between users and images.

The task is to set the profile_pic of a user to the respective image name stored in the image table. You might be familiar with constructing a SELECT query for this purpose, but directly applying this logic in PostgreSQL requires a different approach.

Solution Breakdown

Let's look at the steps needed to achieve the desired update.

1. Basic Update Syntax in PostgreSQL

PostgreSQL provides a specific syntax for updating tables that involve joins. Here's a straightforward way to perform the update operation:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code:

UPDATE Statement: The SQL begins with UPDATE to specify the table we want to update—in this case, the user table aliased as u.

SET Clause: We set the profile_pic field to the corresponding name from the image table using the alias i.

FROM Clause: Here, you join the necessary tables. You join user_images_image to relate users and images and then join the image table to get the names.

WHERE Clause: The critical filters include matching the user ID and the image ID. The ? placeholders can be substituted with actual values.

2. Alternative Using a Correlated Subquery

If you prefer a different approach or if there are situations where no images are available, consider using a correlated subquery:

[[See Video to Reveal this Text or Code Snippet]]

Key Points about the Subquery:

Correlated Subquery: The subquery retrieves the name of the image directly related to the user by correlating the userId.

WHERE Clause: It maintains a condition to restrict updates to the targeted user.

Conclusion

Updating values across tables in PostgreSQL, especially with complex many-to-many relationships, can initially seem daunting. However, by employing the appropriate UPDATE syntax or leveraging a correlated subquery, you can efficiently achieve your goals.

Now you have two practical methods to update a user's profile picture based on images from a separate table. Experiment with these queries in your PostgreSQL environment, and you'll find handling relational updates to be a valuable skill in your database management toolkit.