Скачать или смотреть How to Update Multiple Rows in a Single SQL Statement

Learn how to update multiple rows in an SQL table simultaneously to avoid errors and ensure data integrity.
---
This video is based on the question https://stackoverflow.com/q/70010290/ asked by the user 'Jekt' ( https://stackoverflow.com/u/8895437/ ) and on the answer https://stackoverflow.com/a/70010612/ provided by the user 'Sean Lange' ( https://stackoverflow.com/u/3813116/ ) 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 multiple rows within the same table in one SQL statement?

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.
---
Introduction

Updating records in a database is a common task that can become complicated when dealing with multiple rows and specific conditions. Have you ever found yourself needing to simultaneously update different values in different rows of the same column within a single SQL statement? If so, you're not alone! Many developers face this challenge, particularly when trying to set default values or swap existing values.

In this guide, we'll dive into a specific example and provide a clear and concise solution to achieve this, enabling you to update your SQL table efficiently and effectively. Let's explore how to do it!

Understanding the Problem

To illustrate, let's consider the following scenario: You have a table with procedure code categories, and you need to swap the DefaultForAxisID values between two different CategoryIDs (21 and 22) for the same ProcedureCodeID.

In our example, the tasks you want to accomplish are:

Set DefaultForAxisID = 2 where ProcedureCodeID = 195837 and CategoryID = 21.

Set DefaultForAxisID = null where ProcedureCodeID = 195837 and CategoryID = 22.

When executed separately, you might encounter errors, as some SQL systems prohibit clearing an existing default without assigning a new one. So, how do you swap these values without generating errors?

Solution: Using a CASE Statement

The good news is that there is a straightforward solution utilizing the CASE statement in your SQL query. This approach allows you to update multiple rows in one go without dealing with subsequent error messages.

SQL Update Syntax

Here’s the SQL statement you can use:

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

Explanation of the SQL Statement

UPDATE ProcedureCodeCategory: Specifies the table you are updating.

SET DefaultForAxisID = CASE: Begins the conditional update where DefaultForAxisID will receive new values based on the CategoryID.

WHEN CategoryID = 21 THEN 2: If the CategoryID is 21, set DefaultForAxisID to 2.

WHEN CategoryID = 22 THEN NULL: If the CategoryID is 22, set DefaultForAxisID to NULL.

ELSE DefaultForAxisID: If neither condition is met, keep the current value of DefaultForAxisID (ensuring we don't affect other records).

WHERE CategoryID IN (21, 22): Limits the scope of the update to only the rows with the specified CategoryIDs.

Resulting Data

After executing the above update query, the records in your ProcedureCodeCategory table will reflect the intended swaps:

For CategoryID = 21, DefaultForAxisID is set to 2.

For CategoryID = 22, DefaultForAxisID is set to NULL.

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

Conclusion

Updating multiple rows in a single SQL statement is possible and can save you from unnecessary errors and complexity. By using a CASE statement, you can assign new values conditionally while maintaining the integrity of your database.

The method outlined above can be implemented in a stored procedure and can be further integrated into applications, such as a Python function, to automate the process.

Now, with this knowledge, you can tackle similar issues with confidence, ensuring your database updates run smoothly and efficiently!