Скачать или смотреть How to Make an UPDATE Without a Subquery in PostgreSQL

Learn how to simplify your PostgreSQL updates by using the `UPDATE ... FROM` clause instead of nested subqueries.
---
This video is based on the question https://stackoverflow.com/q/74013964/ asked by the user 'TK123' ( https://stackoverflow.com/u/20204183/ ) and on the answer https://stackoverflow.com/a/74014401/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) 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: How to make update without subquery in postgresql

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.
---
Streamlining PostgreSQL Updates: Avoiding Subqueries

When working with databases, efficient querying is key to maintaining performance and clarity. One common task is updating records based on related table information. PostgreSQL provides several ways to handle updates, and in this guide, we will explore how to simplify your updates by using UPDATE ... FROM, avoiding the necessity of using subqueries.

The Problem: A Nested Subquery for Updates

Imagine you have two tables in your PostgreSQL database: sportsman and sportsman_result. You want to update a value in the sportsman_result table based on an identifier found in the sportsman table. Your original query looks something like this:

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

While this query achieves the desired result, it has a couple of downsides:

It relies on a subquery, which can be less efficient.

It clutters the SQL with unnecessary complexity.

The Solution: Using UPDATE ... FROM

PostgreSQL allows you to streamline this operation with a more effective technique. By using the UPDATE ... FROM clause, you can achieve the same outcome without nested queries. Here's how you can refactor the initial query:

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

Breakdown of the Query

UPDATE Clause: Specifying the table and alias (sportsman_result AS sr) you want to update.

SET Clause: Defining the new value for the shown_result column, adding one to its current value.

FROM Clause: Joining the sportsman table (FROM sportsman AS s), which will allow you to reference its columns in the WHERE condition.

WHERE Clause: Setting the conditions for the update:

sr.sportsman_result_id = s.sportsman_result_id: Ensures you're only updating results that match on the sportsman_result ID.

s.sportsman_id = 1: Limits updates to those related to a specific sportsman.

Why This Method is More Efficient

Clarity: The modified query is cleaner and easier to understand without the nested logic.

Performance: Avoiding subqueries can lead to improved execution times, especially with large datasets.

Data Integrity: It maintains a clear reference point to other tables, which can help prevent errors.

Conclusion

When working in PostgreSQL, it's beneficial to leverage the full capabilities of SQL, such as the UPDATE ... FROM clause. This technique not only simplifies your SQL queries but also enhances performance and readability. Avoiding unnecessary complexity in your updates can save time and reduce the likelihood of mistakes in your code.

By employing methods like this, you'll take a step towards mastering efficient database interactions, making your coding experience smoother and more productive.

Happy querying!