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

Learn how to effectively update multiple rows in SQL using subqueries and joins, solving common issues encountered in database management.
---
This video is based on the question https://stackoverflow.com/q/65235988/ asked by the user 'Dennis Wojtyczka' ( https://stackoverflow.com/u/14801465/ ) and on the answer https://stackoverflow.com/a/65236011/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) 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 can I UPDATE more rows in a subquery?

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.
---
Mastering SQL Updates: How to Update More Rows in a Subquery

When working with databases, updating multiple rows simultaneously can often seem like a daunting task, especially when dealing with complex queries that involve subqueries. In this guide, we'll explore a common scenario involving two tables: Rawdata and Cleandata. We will guide you through a clear solution for updating multiple rows in SQL effectively and efficiently.

The Problem

Suppose you maintain two tables in your SQL database:

Rawdata: This table regularly receives IDs and distances for various shuttles.

Cleandata: This table is intended to summarize the distances from Rawdata, but it only contains one entry for each unique shuttle ID.

Example Data

To better illustrate the problem, let's take a look at how the data might be structured within these tables:

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

When attempting to update the Distance field in Cleandata by using a summation of the distances from Rawdata, you might run into an error if there are multiple IDs involved. For example:

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

This query returns the error:

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

This error occurs because the subquery results in multiple rows when handling various ShuttleIDs.

The Solution

To effectively resolve this problem, we can use either a correlated subquery or a JOIN. Below are the methods:

1. Correlated Subquery

A correlated subquery allows you to calculate the SUM of distances for each shuttle as you iterate through the Cleandata table. Here’s how you can do it:

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

2. Using JOIN

If you only want to update rows in Cleandata that have matching ShuttleIDs in Rawdata, you can use a JOIN statement:

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

3. Including Current Distance

If you wish to include the current distance in the Cleandata table instead of replacing it entirely, you can modify the SET clause as follows:

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

Conclusion

By using these SQL techniques, you can successfully update multiple rows at once, preventing common errors during database management. Whether you choose a correlated subquery or a JOIN, both methods are efficient for summing up distances from one table to update another. This knowledge will not only enhance your SQL skills but also improve your overall database handling.

Feel free to try out these queries in your database, and soon updating multiple rows in a subquery will become a breeze!