MySQL Tutorial
MySQL Installation and Configuration
MySQL Database Operations
Database Design
MySQL Data Types
MySQL Storage Engines
MySQL Basic Operations of Tables
MySQL Constraints
MySQL Operators
MySQL Function
MySQL Manipulate Table Data
MySQL View
MySQL Indexes
MySQL Stored Procedure
MySQL Trigger
MySQL Transactions
MySQL Character Set
MySQL User Management
MySQL Database Backup and Recovery
MySQL Log
MySQL Performance Optimization
In MySQL, you can update multiple tables at once using the multi-table UPDATE syntax. This can be extremely useful when tables are related to each other.
Here is the syntax:
UPDATE table1
INNER JOIN table2
ON table1.common_field = table2.common_field
SET table1.target_column = value_or_expression,
table2.target_column = value_or_expression
WHERE condition;
Example:
Consider the following two tables:
orders table:
| id | customer_id | product | quantity |
|---|---|---|---|
| 1 | 101 | Apple | 10 |
| 2 | 102 | Banana | 5 |
| 3 | 103 | Cherry | 15 |
customers table:
| id | name |
|---|---|
| 101 | John Doe |
| 102 | Jane Doe |
| 103 | Jim Smith |
Let's say you want to update the product in the orders table to 'Orange' and the name in the customers table to 'Updated Customer' where the customer_id is '101'. Here is how you would do it:
UPDATE orders o
INNER JOIN customers c ON o.customer_id = c.id
SET o.product = 'Orange',
c.name = 'Updated Customer'
WHERE o.customer_id = 101;
After executing the above SQL, the 'Apple' product associated with 'John Doe' will change to 'Orange', and the name 'John Doe' will change to 'Updated Customer'.
Important Notes:
Be very careful when using multi-table update as it can update large amounts of data at once, and you cannot undo this operation.
If the UPDATE statement includes an ORDER BY clause, the rows are updated in the order that is specified but the ORDER BY clause is valid only in single-table updates. In multiple-table updates, it is undefined which rows are updated first.
You can also use LEFT JOIN instead of INNER JOIN depending on your requirements.
MySQL UPDATE Multi-Table Example:
UPDATE table1, table2 SET table1.column1 = value1, table2.column2 = value2 WHERE table1.common_column = table2.common_column AND condition;
How to Perform Multi-Table Updates in MySQL:
UPDATE customers, orders SET customers.status = 'Gold' WHERE customers.customer_id = orders.customer_id AND orders.total_amount > 1000;
Updating Multiple Tables with JOIN in MySQL:
UPDATE products INNER JOIN inventory ON products.product_id = inventory.product_id SET products.stock_status = 'In Stock' WHERE inventory.quantity > 0;
MySQL UPDATE with INNER JOIN for Association Updates:
UPDATE employees INNER JOIN departments ON employees.department_id = departments.department_id SET employees.manager_id = 1001 WHERE departments.department_name = 'IT';
Multi-Table Updates Using Aliases in MySQL:
UPDATE c SET c.status = 'Platinum' FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id WHERE o.total_amount > 5000;
Using Subqueries in MySQL UPDATE for Associations:
UPDATE employees SET salary = salary * 1.1 WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');
Updating Related Records in MySQL with JOIN:
UPDATE products INNER JOIN categories ON products.category_id = categories.category_id SET products.price = products.price * 1.05 WHERE categories.category_name = 'Electronics';
Examples of Complex Updates with MySQL Multi-Table Association:
UPDATE employees SET bonus = bonus + 500 WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York') AND salary > 70000;
UPDATE orders SET total_amount = total_amount * 1.1 WHERE customer_id IN (SELECT customer_id FROM customers WHERE status = 'Platinum') AND order_date < '2023-01-01';