Скачать или смотреть How to Efficiently Update Rows in MySQL Using Data from Another Table

Discover how to update the `lessonId` and `uniqueId` in your MySQL `TestResults` table by fetching the data from the `Progress` table using a simple SQL query.
---
This video is based on the question https://stackoverflow.com/q/62783461/ asked by the user 'Node.JS' ( https://stackoverflow.com/u/1834787/ ) and on the answer https://stackoverflow.com/a/62783639/ provided by the user 'forpas' ( https://stackoverflow.com/u/10498828/ ) 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: mySQL foreach row run an update

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 Efficiently Update Rows in MySQL Using Data from Another Table

Managing databases can sometimes feel like a juggling act, especially when you need to update information across different tables. In this post, we will address a common problem: how to update rows in one table based on corresponding data from another in MySQL. Specifically, we'll focus on how to update the lessonId and uniqueId in the TestResults table using data from the Progress table.

Understanding the Problem

In our scenario, we have two tables:

Progress Table

Columns: progressId, lessonId, uniqueId

TestResults Table

Columns: progressId, lessonId, uniqueId (the latter two need to be updated)

We need to update the TestResults table's lessonId and uniqueId based on the corresponding progressId linking it to the Progress table. So, for every progressId in TestResults, we want to fetch the related lessonId and uniqueId from the Progress table and perform the update.

The Solution

The key to accomplishing this task lies in using an UPDATE statement along with a JOIN. The SQL syntax for updating can be tricky, but we can break it down into clearer steps.

Step-by-Step Guide

Set Up the Update Statement: To begin, we need to dictate which table we are updating – in this case, the TestResults table.

Join the Tables: We then join the Progress table to associate the correct data with the corresponding progressId.

Set the Values to Update: Finally, we use the SET clause to define what we want to change in the TestResults table.

The SQL Query

Here is the SQL query that accomplishes this:

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

Breakdown of the Query:

UPDATE TestResults t: We specify that we are updating the TestResults table and alias it as t for easier reference.

INNER JOIN Progress p: We perform an inner join with the Progress table (aliased as p). This means we only consider rows where there is a match between the two tables.

ON p.progressId = t.progressId: We are linking the tables based on the progressId column. Only those records that have matching progressId in both tables will be involved in the update.

SET t.lessonId = p.lessonId, t.uniqueId = p.uniqueId: Here’s where the magic happens! We set the lessonId and uniqueId in the TestResults table to be equal to the corresponding values from the Progress table.

Conclusion

Updating rows in one table based on data from another doesn’t have to be a daunting task. By leveraging the power of SQL JOINs, you can efficiently perform batch updates and keep your database aligned.

If you're ever faced with similar requirements in your database management tasks, remember this straightforward approach. It can save you time and help maintain data integrity across your tables.

With this knowledge, you can tackle row updates in MySQL with confidence!