How Can I Update Multiple MySQL Tables Simultaneously with a Single Query?

Efficiently Updating Multiple MySQL Tables in One Go

Standard database practice often involves separate update queries for each table. However, MySQL offers a streamlined method to update multiple tables concurrently using a single query. This approach simplifies database interactions, enhances code clarity, and can potentially boost performance by reducing database communication overhead.

The Single-Query Update Technique

The following syntax demonstrates how to update multiple MySQL tables with just one query:

<code class="language-sql">UPDATE TABLE1, TABLE2
SET TABLE1.column1 = value1, TABLE1.column2 = value2,
    TABLE2.column1 = value1, TABLE2.column2 = value2
WHERE condition;</code>

Let's illustrate this with an example involving two tables: Books and Orders.

<code class="language-sql">-- Books table
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    BookName VARCHAR(50),
    InStock INT
);

-- Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    BookID INT,
    Quantity INT
);</code>

Suppose we need to increase the order quantity for OrderID 1002 by 2 and simultaneously decrease the book stock in the Books table. The following query achieves this:

<code class="language-sql">UPDATE Books, Orders
SET Orders.Quantity = Orders.Quantity + 2,
    Books.InStock = Books.InStock - 2
WHERE
    Books.BookID = Orders.BookID
    AND Orders.OrderID = 1002;</code>

This method streamlines database operations, making your code more readable and potentially faster by reducing the number of interactions with the database.

The above is the detailed content of How Can I Update Multiple MySQL Tables Simultaneously with a Single Query?. For more information, please follow other related articles on the PHP Chinese website!