Скачать или смотреть How to Update a MySQL Table from Another Table Excluding Existing Data

Learn how to efficiently update a MySQL table with new data from another table while excluding already existing rows using `INSERT` and `ON DUPLICATE KEY` methods.
---
This video is based on the question https://stackoverflow.com/q/63844185/ asked by the user 'ben' ( https://stackoverflow.com/u/5793775/ ) and on the answer https://stackoverflow.com/a/63844361/ provided by the user 'GMB' ( https://stackoverflow.com/u/10676716/ ) 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: Update column from another MYSQL table excluding already existing data

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 Update a MySQL Table from Another Table Excluding Existing Data

When managing data in a relational database, you often face situations where you need to update one table based on the data from another. A common scenario is when you want to add new entries from a source table to a target table without duplicating existing entries. In this post, we’ll explore how to achieve this in MySQL, specifically focusing on how to update a table (table_2) with data from another table (table_1) while excluding already existing entries.

Understanding the Tables

Let's start by visualizing the structure of the two tables in question:

Table 1 - Source Table:

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

Table 2 - Target Table:

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

In this setup, table_2 already contains some rows, and we want to add the remaining rows from table_1 that do not exist in table_2.

The Incorrect Approach

Initially, you might consider using an UPDATE query to modify table_2 by joining it with table_1. However, as you’ve experienced, this method would update all matching rows instead of inserting new ones and excluding existing data. Here’s the query that was attempted:

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

This query is not suitable for the task, as it would end up updating table_2 with all rows from table_1, causing unintended data changes.

The Correct Solution: INSERT with ON DUPLICATE KEY

Step-by-Step Solution

To add new rows from table_1 into table_2 and update existing entries when necessary, you should use the INSERT statement in conjunction with the ON DUPLICATE KEY UPDATE clause. Let’s break down this approach:

Use the INSERT Statement: This command will allow you to add new rows to table_2 based on the rows selected from table_1.

Specify the ON DUPLICATE KEY Clause: This clause manages what happens when the primary key from table_1 already exists in table_2. In this case, it updates the system_id with the new value.

Here’s how the query looks:

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

Explanation of the Query

INSERT INTO table_2 (id, system_id): We're specifying that we want to insert values into table_2 for the columns id and system_id.

SELECT id, system_id FROM table_1: We're selecting the rows from table_1 which contains the new entries we want to add.

ON DUPLICATE KEY UPDATE system_id = VALUES(system_id): If an entry with the same primary key (id) already exists in table_2, then we'll update the system_id to match the value from table_1.

Benefits of This Approach

Efficiency: You can insert multiple entries in one query, which is generally faster than executing separate INSERT statements for each row.

Data Integrity: It ensures that existing rows are updated appropriately without duplicating rows, maintaining the integrity of your data.

Conclusion

Handling data updates from one table to another in MySQL can be straightforward with the right approach. By utilizing the INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE method, you can efficiently populate table_2 with new entries from table_1 while excluding rows that already exist. Always remember to test queries in a safe environment before applying them to your live database to avoid unintended data issues.

If you have any questions or need further assistance in optimizing your SQL queries, feel free to reach out!