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

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.