How to Efficiently Update a Table in MySQL Using Multiple Joins?

Updating a Table Involving Several Joins in MySQL

When working with complex database queries involving multiple joins, updating a particular table can pose challenges. While the order of joins may not significantly impact performance, it can affect the ease of writing and reading the query.

Consider the following example, where the goal is to update the tableB table:

UPDATE b
FROM tableA a
JOIN tableB b
   ON a.a_id = b.a_id
JOIN tableC c
   ON b.b_id = c.b_id
SET b.val = a.val+c.val
WHERE a.val > 10
    AND c.val > 10;

In the above query, updating tableB involves tables tableA and tableC through joins. However, tableB is not the first table mentioned in the FROM clause.

Unlike Microsoft SQL Server, MySQL handles multi-table updates differently. The UPDATE syntax does not require specifying the table to be updated explicitly. Instead, the SET clause implicitly updates the table containing the column being assigned a new value.

To achieve the desired update using MySQL syntax, modify the query as follows:

UPDATE tableA a
JOIN tableB b
   ON a.a_id = b.a_id
JOIN tableC c
   ON b.b_id = c.b_id
SET b.val = a.val + c.val
WHERE a.val > 10
    AND c.val > 10;

MySQL's UPDATE syntax allows you to update a table that is not the first one in the FROM clause by using the column name in the SET clause.

The above is the detailed content of How to Efficiently Update a Table in MySQL Using Multiple Joins?. For more information, please follow other related articles on the PHP Chinese website!