Скачать или смотреть How to Update a Field in a MYSQL Table with Results from a Query

Learn how to effectively update fields in a MYSQL table by utilizing the results of queries executed on the same database. This guide provides step-by-step instructions and examples.
---
This video is based on the question https://stackoverflow.com/q/64854055/ asked by the user 'MASHETI ALEX' ( https://stackoverflow.com/u/14646068/ ) and on the answer https://stackoverflow.com/a/64854095/ provided by the user 'juergen d' ( https://stackoverflow.com/u/575376/ ) 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 can I update a field in a MYSQL table with the result of a query over the same database 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.
---
Updating a Field in a MYSQL Table

Updating a specific field in a MYSQL table can sometimes be a complex task, especially when you want to set that field based on the results of another query executed on the same table. In this post, we will explore a common scenario where you have a students database with an empty email field, and you want to populate it using the students' registration numbers and full names.

The Problem

You have a MYSQL table named students consisting of the following fields:

regNumber: The registration number of the student.

fullName: The full name of the student.

email: The email field that is currently empty.

You can generate email addresses using the regNumber and fullName columns, but you need to update all records in the email field with these generated addresses.

Generating Email Addresses

To create the email addresses from the fullName and regNumber, you can use the following SQL query:

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

Explanation of the Query

SUBSTRING_INDEX(): This function helps to extract parts of the fullName.

The first part extracts the first name's initial.

The second part takes the last name.

SUBSTRING(): This function fetches the last four characters of the regNumber.

CONCAT(): Combines these parts with an email domain to create the complete email address.

REPLACE(): Ensures any unwanted characters (like backticks) are removed.

Now, we need to take this result and use it to update the email field for each student record.

Updating the Email Field

To update the email field with the generated email addresses, you can use the following SQL command:

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

How This Works

The UPDATE statement specifies the table to be modified (students).

The SET clause assigns the value for the email field using the same logic from the SELECT query.

The SQL engine will execute this update for every record in the students table, populating each record's email field with the newly generated email addresses.

Final Thoughts

By using a clear SQL query structure, you can efficiently update fields in a MYSQL table based on the results derived from the same table. This method not only saves time but also ensures data consistency across your database. Now, you can easily maintain accurate and comprehensive records for your students.

Feel free to reach out for any clarifications or further assistance with MYSQL queries!