How to Update a Joined Table in MySQL When the Target Table Isn\'t First?

Updating a Joined Table in MySQL with Reverse Table Order

The task of updating a table involved in a join statement can be challenging when the desired table is not located at the beginning. In MySQL, unlike SQL Server, the syntax for multi-table updates differs, addressing this issue.

Consider the following example where the objective is to update table 'b':

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 MySQL, the syntax of the UPDATE statement with a JOIN clause does not specify the table to be updated. Instead, it's implicitly determined by the SET clause.

Hence, to achieve the desired update, the following revised syntax can be used:

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;

It's important to note that the UPDATE with JOIN syntax is not part of the standard SQL specification, and both MySQL and Microsoft SQL Server have implemented their own variations.

The above is the detailed content of How to Update a Joined Table in MySQL When the Target Table Isn\'t First?. For more information, please follow other related articles on the PHP Chinese website!