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

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

DDD
Release: 2024-12-11 07:38:11
Original
561 people have browsed it

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:

1

2

3

4

5

6

7

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;

Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template