MySQL 8: How to update multiple tables in a single query

Introduction

In this tutorial, you will learn how to update multiple tables in a single query using MySQL 8. Updating multiple tables in a single query can be a powerful feature that can save time and help maintain atomicity in your transactions. This feature is often used to ensure that changes across multiple tables are made consistently.

Prerequisites:

• MySQL 8 installed and running
• Basic understanding of SQL
• Access to a database user with sufficient privileges

Understanding JOINs in UPDATE Queries

Before diving into the actual update operation, one should understand that in MySQL, JOIN clauses can be utilized within the UPDATE query itself. This allows for the modification of rows in multiple tables based on a related column, typically a foreign key.

Example:

UPDATE table1
  INNER JOIN table2 ON table1.id = table2.fk_id
  SET table1.column1 = 'value1', table2.column2 = 'value2'
  WHERE table1.condition_column = 'some_value';

This is a basic example of an UPDATE Join. It shows how to change values in two tables where there’s a relationship between them.

Update Using INNER JOIN

INNER JOIN selects records that have matching values in both tables. Combined with UPDATE, it’s used to update rows in multiple tables that match a given condition.

Basic Example:

UPDATE products
  INNER JOIN prices ON products.id = prices.product_id
  SET products.stock = products.stock - 1,
      prices.last_updated = NOW()
  WHERE products.id = 42;

The above query updates the stock count in the ‘products’ table and the last update timestamp in the ‘prices’ table for the product with an ID of 42.

Update Using LEFT JOIN

LEFT JOIN returns all records from the left table and matched records from the right table. When we use this with UPDATE, it’s to update rows even if there is no corresponding match in the JOINed table.

Example:

UPDATE customers
  LEFT JOIN orders ON customers.id = orders.customer_id
  SET customers.last_order_date = IFNULL(orders.order_date, NOW()),
      customers.order_count = IF(orders.id IS NULL, customers.order_count, customers.order_count + 1)
  WHERE customers.id = 1001;

This query updates the ‘customers’ table by setting the last order date to the most recent order date or the current date if no orders are present, and increments the order count if there is at least one order for the customer.

Conditional Multi-Table Update

You may want to update tables under more complex conditions. MySQL allows for this with a more extensive use of the WHERE clause.

Complex condition example:

UPDATE tableA
  INNER JOIN tableB ON tableA.id = tableB.a_id
  SET tableA.status = CASE
      WHEN tableB.priority = 'High' THEN 'Urgent'
      WHEN tableB.priority = 'Low' THEN 'Normal'
      ELSE tableA.status
    END,
    tableB.updated_at = NOW()
  WHERE tableA.id IN (SELECT id FROM tableC WHERE status = 'Active');

This example highlights how to set different statuses in ‘tableA’ and updates the timestamp in ‘tableB’, only for records that have a related active status in ‘tableC’.

Modifying Multiple Rows Across Multiple Tables

In some cases, you might want to update many rows across multiple tables. An effective way to achieve this is by using subqueries or temporary tables.

Example:

CREATE TEMPORARY TABLE TempIDs AS
  SELECT id FROM products WHERE condition = TRUE;

UPDATE products
  INNER JOIN TempIDs ON products.id = TempIDs.id
  INNER JOIN suppliers ON suppliers.id = products.supplier_id
  SET products.price = products.price * 1.1,
      suppliers.last_order_date = NOW();

DROP TEMPORARY TABLE TempIDs;

The preceding statements create a temporary table to hold IDs that meet a certain condition. It then updates the price in the ‘products’ table and the ‘suppliers’ table ‘last order date’, followed by dropping the temporary table.

Transactional Updates Across Multiple Tables

It’s often important to execute updates across multiple tables within the context of a transaction, to maintain database consistency.

Transactional update example

Suppose you have two tables, orders and customers, and you need to update both tables in a single, atomic operation:

-- Start the transaction
BEGIN;

-- Update statement for the first table (e.g., orders)
UPDATE orders
SET status = 'Completed'
WHERE order_id = 1234;

-- Update statement for the second table (e.g., customers)
UPDATE customers
SET last_order_date = NOW()
WHERE customer_id = (SELECT customer_id FROM orders WHERE order_id = 1234);

-- If no errors, commit the transaction
COMMIT;

Here:

• BEGIN;: This statement initiates the transaction. After this point, changes made by subsequent SQL statements are not immediately committed to the database.
• Update Statements: The UPDATE statements modify data in the orders and customers tables. These are just examples; your actual update queries will depend on your database schema and business logic.
• COMMIT;: If all the update statements execute successfully, the COMMIT statement is issued to permanently apply all the changes made during the transaction. If any statement within the transaction block fails, you should issue a ROLLBACK; statement instead to undo all the changes made in the current transaction.

Conclusion

Updating multiple tables utilizing a single query in MySQL 8 combines efficiency with relational database integrity. This guide covered how to perform multi-table updates using JOIN clauses, explored relational scenarios, and emphasized the importance of transactions. Removing the need for multiple queries, it simplifies the process of maintaining consistent data across your database schemas.