Скачать или смотреть How to Run an Update Query on a CTE in MySQL: Avoiding Common Errors

Learn how to effectively use Common Table Expressions (CTEs) in MySQL for your update queries without running into errors.
---
This video is based on the question https://stackoverflow.com/q/62414158/ asked by the user 'Pradeep Patel' ( https://stackoverflow.com/u/1138980/ ) and on the answer https://stackoverflow.com/a/62414264/ 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: How do I run an update query in MySQL on a CTE?

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 Run an Update Query on a CTE in MySQL: Avoiding Common Errors

If you've been working with MySQL and trying to run an update query on a Common Table Expression (CTE), you may have encountered some frustrating obstacles. A common error message reads: "Error Code: 1288. The target table pptest of the UPDATE is not updatable." If you’ve faced this issue, you're not alone. Let’s break down the problem and explore how to correctly execute your update queries involving CTEs.

Understanding the Problem

The primary problem arises from attempting to use the CTE syntax in MySQL, which, as many users mistakenly do, is often borrowed from other SQL interfaces, such as SQL Server. In MySQL, directly updating a CTE isn’t supported, which leads to the error message you've likely encountered.

Scenario Overview

You aimed to create a table or view that contained specific records, and you wanted to use the row_number() function to group duplicate data together. Your intention was to flag the duplicates and then update a specific column if certain conditions were met. However, the syntax you provided won't work as expected in MySQL.

The Correct Approach to Update Data in MySQL

To achieve your goal of updating records in a CTE-like manner in MySQL, you should use the UPDATE ... JOIN syntax. This approach allows you to join the table with a subquery, enabling you to apply updates based on the results from this subquery.

Step-by-Step Solution

1. Ensure You Have a Unique Identifier

For the update to work correctly, you need a primary key in your table. This primary key should uniquely identify each row. Let's assume your unique identifier is named id.

2. Utilize an Update Combined with a Join

Here’s how you can structure your query using the UPDATE ... JOIN syntax:

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

Key Notes to Keep in Mind

Ordering and Duplicates: When using the ROW_NUMBER() function to identify duplicates, ensure your ORDER BY clause is deterministic. For example, it is better to use ORDER BY id to ensure that the same duplicate entries are flagged consistently.

MySQL Version Requirement: Window functions like ROW_NUMBER() are only available in MySQL version 8.0 and later. If you’re using an earlier version, you will need to upgrade or explore alternative methods for handling duplicates.

Conclusion

Updating data in MySQL using a CTE isn’t as straightforward as some other SQL dialects. By transitioning to the UPDATE ... JOIN strategy and ensuring you have a valid primary key, you can effectively manipulate your records without encountering the dreaded error messages. With these insights, you can confidently work with your database, keeping your data clean and organized.

Now that you have the knowledge at your fingertips, go ahead and implement this strategy in your own database queries. Happy coding!