MySQL Union table update and deletion syntax introduction, mysql Union table syntax

Source: Internet
Author: User

MySQL Union table update and deletion syntax introduction, mysql Union table syntax

Preface

I believe that when you use mysql in daily use, you may need to update two tables at the same time. I will use two SQL statements in the same transaction to update them separately. In fact, this method of sending two SQL statements is relatively inefficient. Is there any way to complete this operation with only one SQL statement?

The answer is yes. Here is the code record:

Suppose there are two tables:

1. user table, field: id, user_name

2, user_role table, field: id, role_name, user_id

The link isuser.id = user_role.user_id

To change the user_name field of the data in a user table and the role_name field of the data in the user_role table corresponding to the user, use the following SQL statement:

update user, user_role set user.user_name = ?, user_role.role_name = ? where user.id = ? and user.id = user_role.user_id

The table above is written as a join table update. Is there any join table deletion?

The answer is yes. Here is the code record:

Suppose there are two tables:

1. user table, field: id, user_name

2, user_role table, field: id, role_name, user_id

The link isuser.id = user_role.user_id

To delete the data of a user table and the data in the user_role table corresponding to the user, use the following SQL statement:

delete u, u_r from user u, user_role u_r where u.id = ? and u.id = u.user_id

In the preceding SQL statement, u is the alias of user, and u_r is the alias of user_role. the alias must be attached before the from statement. This alias applies to specifying which table to delete data. If only u or u_r is used, then, only the data on the corresponding table is deleted.

Summary

The above is all about this article. I hope this article will help you in your study or work. If you have any questions, please leave a message.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.