How to Update a Table in a MySQL Multi-Join Statement When It\'s Not the First Table?

Update a Table in a Multi-Join Statement in MySQL

Updating a joined table in MySQL can be challenging when the table you want to update is not the first in the join chain. This article explores how to overcome this obstacle using MySQL's unconventional UPDATE syntax.

Unconventional Syntax for Multi-Table Update

Contrary to Microsoft SQL Server's syntax, MySQL's UPDATE with JOIN statement doesn't require specifying the table to be updated in the FROM clause. Instead, it implicitly uses the table specified in the SET clause.

Example

The provided example attempts to update tableB based on values from tablesA and tableC:

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

Key Points

• The SET clause specifies tableB as the table to be updated.
• The FROM clause is omitted as it is not required in MySQL's multi-join update syntax.
• Standard SQL doesn't support UPDATE with JOIN, and MySQL and Microsoft SQL Server have implemented their own extensions to the standard syntax.

By adhering to MySQL's unique syntax, users can successfully update joined tables even when the target table is not the first in the join chain.

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