Скачать или смотреть How to Update Multiple Tables Using Joins in MySQL Without Errors

Learn how to efficiently update multiple tables in MySQL with proper join techniques while avoiding common errors.
---
This video is based on the question https://stackoverflow.com/q/69269646/ asked by the user 'BigDX' ( https://stackoverflow.com/u/1464250/ ) and on the answer https://stackoverflow.com/a/69269846/ provided by the user 'Bill Karwin' ( https://stackoverflow.com/u/20860/ ) 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 multiple tables when using a join Max

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.
---
Updating Multiple Tables in MySQL: Solutions and Techniques

Updating multiple tables in MySQL can often lead to confusion, especially when using joins. A common issue arises when trying to update a target table that isn't designed to be directly updatable, which throws an error. In this guide, we’ll dissect a real-world scenario from Magento 2.0 and provide a clear solution to the problem at hand.

Understanding the Problem

The scenario involves updating product pricing and timestamps in a Magento 2.0 MySQL database. The query presented is intended to adjust the price of products while simultaneously updating the updated_at timestamp. However, this action results in the error "The target table e of the UPDATE is not updatable".

Example Query

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

When the line trying to set e.updated_at is included, the query produces an error, although the query without it executes perfectly.

Why the Error Occurs

The error occurs because you are attempting to update a derived table (the result of a subquery within your JOIN). MySQL doesn't allow updating fields in this way when dealing with multi-table updates that include derived tables.

Solution: Using Joins Correctly

To resolve this issue, we will adjust the query such that it updates the original table directly instead of attempting to update a derived table. Here’s how we can achieve that effectively:

Revised Query

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

Breakdown of the Revised Query

Selecting the Right Rows:

We join catalog_product_entity_decimal with catalog_product_entity on row_id.

A left outer join is made with the same catalog_product_entity table (aliased as e2) to check for other rows with the same entity_id but a greater row_id.

Conditional Updates:

The WHERE e2.row_id IS NULL condition ensures that we only target the rows that have the greatest row_id for their respective entity_id. This effectively narrows down the updates to those rows that should indeed be updated (the most recent).

Setting Values:

The SET clause updates d1.value with the price from the import_price table while also updating the updated_at field in the catalog_product_entity without referencing it directly from a derived table.

Conclusion

Updating multiple tables using joins in MySQL can be tricky, but with a proper understanding of how to structure your queries, you can avoid common pitfalls like the one outlined above.

This technique not only resolves the error but ensures that you're efficiently updating only the necessary records in your database. As you work with MySQL, always remember to check the visibility to update targets to avoid similar issues!

Now that you're equipped with the knowledge to handle such queries, you'll find working with joins in MySQL less daunting and more productive.