Скачать или смотреть How to Patch Existing Data in MySQL Using Values from Another Table

Learn how to effectively merge data from one MySQL table with corrections from another table without overwriting existing valid data.
---
This video is based on the question https://stackoverflow.com/q/65252173/ asked by the user 'Hamza' ( https://stackoverflow.com/u/282733/ ) and on the answer https://stackoverflow.com/a/65252465/ provided by the user 'P.Salmon' ( https://stackoverflow.com/u/6152400/ ) 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: "Patch" existing data on a table from values in another table

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 Patch Existing Data in MySQL Using Values from Another Table

In data management, it's common to encounter situations where you need to correct or update records in a database. This is particularly true when dealing with external systems that feed data into your application. In this guide, we'll explore how to patch existing data in a MySQL table using values from another table containing corrections. Let’s dive into the problem and solution step by step.

The Problem

Imagine you have two tables with the same structure: one table (table1) contains data imported from another system, while the second table (table2) contains the necessary corrections. Here’s a brief overview of our tables:

Table Structure:

table1 (Original Data):

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

table2 (Corrections):

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

In this case, you want to update table1 so that:

For order_number 101, you want to add the name from table2.

For order_number 102, you want to correct the tracking_no, while keeping the existing name and email if they are valid.

The Solution

The goal is to merge the data from table2 into table1 based on the order_number. We also want to apply specific rules for updating values: if table2 has a non-null value, we use it; if it has a null, we keep the value from table1. MySQL provides a convenient way to handle this using the INSERT ... ON DUPLICATE KEY UPDATE syntax.

Step-by-Step Implementation

Creating Tables: First, let’s create both tables and populate them with test data.

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

Merging Data: Next, we will merge the data from table2 into table1 using an insert statement with a duplicate key strategy.

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

Here’s what happens in the query:

If an order_number from table2 does not exist in table1, it will be inserted.

If it already exists (a duplicate key), the CASE statement ensures that only non-null values from table2 overwrite the corresponding values in table1.

Verifying the Results: Finally, let’s check the results to see the updated table1:

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

Expected output:

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

Conclusion

By using the INSERT ... ON DUPLICATE KEY UPDATE method, you can effectively patch existing data in your MySQL tables without losing valuable information. This method allows for efficient data corrections and ensures that only valid updates are applied, keeping your dataset clean and accurate. Whether you're a developer or a data analyst, mastering this technique can streamline your data management tasks significantly.

Now you have a clear way to patch your MySQL tables when you have corrections stored in another table. Go ahead and implement this in your project to make data maintenance a breeze!