How to Run Multiple SQL Updates While Returning Results as Rows in PostgreSQL

Discover how to efficiently execute multiple updates in PostgreSQL while retrieving results in separate rows using SQL queries.
---

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: Update returning * and select result as rows

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

If you're working with PostgreSQL and need to perform multiple UPDATE operations while also returning the updated results, you're likely seeking a streamlined approach to handle this process efficiently. The question many developers face is how to run these updates in a single query while ensuring that the output is not just a single row but multiple rows representing each update. In this post, we will explore an effective solution to this common problem.

The Problem

You may have encountered a scenario where you wanted to execute several updates on a database table and immediately retrieve all the updated rows. For example, you might have UPDATE statements for different rows based on their IDs. Your initial attempt using Common Table Expressions (CTEs) might look something like this:

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

While this query does return results, you found that trying to display results from both updates using the simple syntax of SELECT:

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

Only resulted in a single row being displayed, rather than the distinct rows for each update operation. This is where the solution comes into play.

The Solution

To address this issue, you can take advantage of the UNION ALL SQL operation. The UNION ALL operator allows you to combine the results of two or more SELECT statements into a single result set, achieving the desired effect of returning multiple rows for each update made. Here’s how to properly implement it:

Step-by-Step Implementation

Update and Return Rows: First, use the RETURNING clause for your updates so that you can capture the output of what has been modified in the table.

Combine Results: Utilize the UNION ALL to combine the outputs of these updates into a coherent result set.

Here’s how your SQL query should look once corrected:

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

Explanation of the Components

WITH Clause: The WITH clause is used to define the Common Table Expressions (CTEs) foo and bar, performing the updates and returning the modified rows.

Update Statement: Each update statement is followed by RETURNING *, ensuring that all columns of the modified rows are returned.

UNION ALL: This operator stacks the results from both CTEs into a single output, preserving all rows even if they are identical (unlike UNION, which removes duplicates).

Why Use UNION ALL?

Preserves All Results: Unlike UNION, which filters out duplicate rows, UNION ALL retains every row from both result sets.

Efficiency: Using CTEs allows for cleaner and more readable queries, promoting better understanding and maintenance of your SQL code.

Flexibility: It allows you to easily incorporate additional updates or modifications in future queries.

Conclusion

Effectively executing multiple SQL updates while capturing and returning results can significantly enhance the productivity and efficiency of your database operations. By using the UNION ALL approach alongside CTEs, you can retrieve updated rows in a clear and organized way. This method not only makes your SQL queries more readable but also provides flexibility and robustness in handling database modifications.

Next time you need to run multiple UPDATE statements in PostgreSQL while retrieving the results as separate rows, remember to leverage the power of UNION ALL to achieve that goal!