Скачать или смотреть How to Update with Join in Multiple Tables Using PostgreSQL

Discover the complete guide on how to efficiently `update` records in multiple tables using SQL joins in PostgreSQL, focusing on retaining only the maximum values while updating duplicates.
---
This video is based on the question https://stackoverflow.com/q/71386412/ asked by the user 'None' ( https://stackoverflow.com/u/4511325/ ) and on the answer https://stackoverflow.com/a/71387718/ provided by the user 'Edouard' ( https://stackoverflow.com/u/8060017/ ) 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 update with join multiple tables?

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 with Join Multiple Tables in PostgreSQL

Updating records in SQL can be tricky, especially when dealing with multiple tables and the need to selectively update duplicates. One common scenario occurs when you want to keep the row with the maximum value and update other duplicates. In this guide, we'll explore how to achieve this using PostgreSQL with a detailed explanation of the process, complete with code examples.

The Problem

Imagine you have a table omt_order_item where you need to update the status and substatus of certain records based on specific conditions. The challenge arises when you want to retain only one record with the maximum value for a specific column while updating other duplicates. Your original SQL query mistakenly updates all records that aren't the maximum, failing to target duplicates correctly.

Example Scenario

You have three records, and you wish to update only two of them—those that have values less than the maximum. With the right query, you can efficiently perform this operation without altering the intended maximum value row.

The Solution

To effectively update rows while preserving the maximum value, you can use a Common Table Expression (CTE) to first identify the maximum values and then apply the update based on those results. Let’s break down the solution step by step.

Step 1: Identify the Maximum Values

Using a CTE, we can select the maximum order_item_num for each group of duplicates. The SQL code for this step is as follows:

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

Step 2: Perform the Update

Now that we have a list of the maximum order_item_num values, we can proceed with the update. Here is how you can structure your update query:

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

Explanation of the Update Logic

Update Target Table: The update is targeted at the omt_order_item table, where we set the status and substatus to CANCELED.

Join Condition: We join the derived list from the CTE to match fr_acount_id and ensure that we are only affecting relevant records.

Exclusion of Max Value: The crucial part is the condition AND ooi.order_item_num <> l.max_order_item_num, which excludes the record with the maximum value from being updated.

Conclusion

By using a CTE in combination with an efficient update query, you can solve the problem of updating duplicates while retaining the maximum value in your PostgreSQL database. This approach not only simplifies the process but also enhances the accuracy of your data manipulation.

Feel free to experiment with this method in your SQL environment and tailor it to fit your specific needs. Happy querying!