How to Update a Joined Table in MySQL?

MySQL Update a Joined Table

When working with a database, it is often necessary to update records based on multiple conditions from different tables. This can be achieved using join statements. However, the syntax for updating joined tables in MySQL differs from Microsoft SQL Server.

In Microsoft SQL Server, you would start by specifying the table to be updated in the UPDATE statement, followed by join statements and the SET clause. However, in MySQL, the syntax is different. The table to be updated is implicit in the SET clause.

Consider the following dummy example:

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, this statement will successfully update the b table based on the specified conditions. The FROM clause is not necessary in the MySQL syntax, as the table being updated is determined by the SET clause.

It is important to note that updating joined tables is not part of the standard SQL syntax. MySQL and Microsoft SQL Server have implemented their own extensions to the standard for handling this operation. Therefore, the syntax may vary between different database management systems.

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