Скачать или смотреть How to Update MySQL Table Data: Inserting Data from One Table to Another

Learn how to effectively transfer data between two MySQL tables. This guide focuses on updating rows in one table based on values in another, ensuring seamless database management.
---
This video is based on the question https://stackoverflow.com/q/71005146/ asked by the user 'basetroll' ( https://stackoverflow.com/u/15381427/ ) and on the answer https://stackoverflow.com/a/71005194/ 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: Insert data from one table to another

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 Data Transfer in MySQL: Updating Table Values

When working with databases, a common task is to transfer data from one table to another. Whether you're a beginner or an experienced user, the fundamental process of handling data updates is essential. In this post, we'll tackle how to update data in one MySQL table based on the values from another table.

The Problem: Why You Need to Insert Data from One Table to Another

Imagine you have two tables in your MySQL database: Table A and Table B. Below is the structure of each table:

Table A

idTagNameSize1OneBob14.292TwoCharles28.563ThreeFrank76.95Table B

idTagNameSize55TwoCharles068ThreeFrank0In this scenario, you wish to update the Size in Table B using the corresponding values from Table A based on matching Tag and Name. However, you encountered an error while trying to execute the following query:

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

The error displayed was: ERROR 1054 (42S22): Unknown column 'B.Tag' in 'where clause'.

This stems from a misunderstanding of how to properly reference columns from the two tables in relation to inserting data.

The Solution: Update Instead of Insert

Instead of trying to insert new rows into Table B, you should update the existing rows. To achieve this, you can use an UPDATE statement combined with a JOIN. This allows you to update Table B's values directly using the corresponding values from Table A based on the matching criteria.

Here’s how you can do it:

Step-by-Step Process

Structure the UPDATE Statement:
The SQL UPDATE statement should use an INNER JOIN to link the two tables based on shared Tag and Name attributes.

Execute the Update Query:
Run the following SQL command to transfer the Size values from Table A to Table B:

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

Breakdown of the Query:

UPDATE B: Specifies that you are updating rows in Table B.

INNER JOIN A ON A.Tag = B.Tag AND A.Name = B.Name: This part finds matching rows between Table A and Table B based on Tag and Name.

SET B.Size = A.Size: This assigns the Size value from Table A to Table B for each matched row.

Conclusion

By using an UPDATE statement with an INNER JOIN, you can efficiently transfer data from one MySQL table to another based on specific criteria. This approach is not only effective but also helps to avoid errors encountered during insertion attempts.

Remember, understanding the nuances of SQL is key as you continue your database journey. Happy coding!