Скачать или смотреть Mastering SQL: Effortlessly Update Multiple Rows with Matching Data Between Two Tables

Learn how to *update multiple rows* in MySQL by merging data from one table to another based on a unique identifier. Follow our easy guide to achieve this with clarity and confidence!
---
This video is based on the question https://stackoverflow.com/q/69055223/ asked by the user 'Mohan Wijesena' ( https://stackoverflow.com/u/4196889/ ) and on the answer https://stackoverflow.com/a/69055229/ 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: SQL UPDATE multiple rows of matching data between two 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.
---
Mastering SQL: Effortlessly Update Multiple Rows with Matching Data Between Two Tables

When working with databases, you might encounter situations where you need to update records in one table based on values in another. This is especially important when you're dealing with relational data. In this post, we'll tackle a specific problem: how to update multiple rows in MySQL when you need to merge data from two tables using a unique identifier. We will explain it in a clear and organized manner, so you can easily follow along.

The Problem

Let's say you have two tables containing inventory data: Table1 (your main source of data) and Table2 (the table with updated values). The key point here is that both tables share a common column, called CODE, which serves as a unique identifier for each row.

Here’s a quick look at the tables:

Table1:

CODEQTYA10B15C20Table2:

CODEQTYB10C5Your goal is to update the QTY in Table1 by adding the corresponding values from Table2 for matching CODE values. The expected outcome would look like this:

Updated Table1:

CODEQTYA10B25C25The Solution

To achieve this update properly, you can use SQL’s JOIN functionality. Here’s how you can do it step-by-step:

Step 1: Understanding the Join

A join combines rows from two or more tables based on a related column between them. In this case, we need to join Table1 and Table2 using the CODE column.

Step 2: Using Aggregate Functions (if necessary)

If there are multiple entries in Table2 for the same CODE, it's a good practice to first aggregate those values. This is done using the SUM function to ensure you are working with the correct totals.

Step 3: The SQL Command

Here’s the SQL command that you might want to use:

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

This command does the following:

Joins Table1 (t1) with the aggregated values from Table2 (t2).

Groups the results by CODE to ensure only one entry per CODE.

Updates the QTY in Table1 by adding the QTY from the aggregated results.

Step 4: Simple Join Case (when codes are unique)

If you are sure that CODE values in Table2 are unique, you can simplify the query by skipping the aggregation step:

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

In this case, the JOIN directly adds the QTY from Table2 to Table1 without needing to sum the values first.

Conclusion

Updating multiple rows in SQL by merging data from two tables is a straightforward task once you understand how to utilize JOIN. Whether you need to aggregate values or can work with unique ones, the SQL commands we discussed will help you smoothly update your records.

Now, get out there and apply these SQL skills to manage your database like a pro!