How to Update a Table in a MySQL Multi-Join Statement?

Update a MySQL Table in a Multi-Join Statement

When dealing with multiple joins in MySQL, understanding the different syntax for updating a joined table is crucial. Unlike in SQL Server, MySQL employs a distinct approach.

Problem:

Performing an update operation on a table involved in a series of joins can be challenging when the desired table is not the first in the join order.

Sample Query:

Let's consider a hypothetical query:

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;

Solution:

In MySQL, the UPDATE syntax with JOIN works differently. The table to be updated is not explicitly specified in the statement; instead, it is inferred from the SET clause. Therefore, the correct query would be:

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;

Key Points:

1. MySQL's UPDATE with JOIN syntax does not require a FROM clause.
2. The table to be updated is specified in the SET clause.
3. This non-standard syntax is a MySQL-specific extension.

The above is the detailed content of How to Update a Table in a MySQL Multi-Join Statement?. For more information, please follow other related articles on the PHP Chinese website!