Summary of Mysql update multi-table joint update method

Next, I will create two tables and execute a series of SQL statements. By carefully observing the changes in the data in the tables after the SQL statements are executed, it is easy to understand the usage of multi-table joint update.

student table class table

1. Execute UPDATE student s , class c SET s.class_name='test00',c.stu_name='test00' WHERE s.class_id = c.id

student table class table

2. Execute UPDATE student s JOIN class c ON s.class_id = c.id SET s.class_name='test11',c.stu_name='test11'

student table class table

3. Execute UPDATE student s LEFT JOIN class c ON s.class_id = c.id SET s.class_name='test22',c.stu_name='test22'

student table class table

4. Execute UPDATE student s RIGHT JOIN class c ON s.class_id = c.id SET s.class_name='test33',c.stu_name='test33'

student table class table

5. Execute UPDATE student s JOIN class c ON s.class_id = c.id SET s.class_name=c.name , c.stu_name=s.name

student table class table

Additional knowledge points:

MySQL multi-table association update

In daily development, we usually write single-table update statements, and rarely write updates that associate multiple tables.

Unlike SQL Server, in MySQL, there are some minor differences in the methods used for multi-table join updates and multi-table join queries for select.

Let’s look at a specific example.

update orders
  left join users u
    on o.userId = u.id
set o.userName = u.name;

In the above example, the update keyword is followed by a result set of a multi-table association. MySQL directly regards this multi-table association result set as a single table, and then performs regular update operations based on this single table.

The slight difference from SQL Server is that the set clause of SQL Server follows the specific table to be updated, while the set statement of MySQL follows the result set to be updated (the specific table to be updated is set in the set statement).

Summarize

This concludes this article on the summary of MySQL update multi-table joint update methods. For more relevant MySQL update multi-table joint update content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!