How to Update Multiple Tables With One Query in MySQL

1. Advantages of Updating Multiple Tables With One Query in MySQL
2. Use the UPDATE Keyword to Update Multiple Tables With One Query in MySQL

There can be cases when a user wants to make simultaneous updates in the logically related table. These logically related tables get linked to each other via some attributes.

Advantages of Updating Multiple Tables With One Query in MySQL

Similar attributes within the tables are used to create an update query. The update query performs multiple row updates in different tables based on conditions.

The multiple tables update queries are fit when a similar situation arises. The benefits of using this multiple tables update query are:

1. This leads to updates in rows at once instead of making individual updates in each table.
2. This also reduces the overall time to update entries in different tables.
3. This reduces the chances of inconsistent updates in tables.
4. Similar response from all the tables at any given time.

Use the UPDATE Keyword to Update Multiple Tables With One Query in MySQL

In the multiple tables update query, each record satisfying a condition gets updated. Even if the criteria are matched multiple times, the row is updated only once.

The syntax of updating multiple tables cannot be used with the ORDER BY and LIMIT keywords.

The syntax for the UPDATE keyword:

UPDATE table1, table2, ...
    SET column1 = value1,
        column2 = value2,
        ...
[WHERE conditions]

Multiple Update is not a combined keyword present in the MySQL language. The syntax gets formed by the combination of various keywords that helps in the grouping of two or more tables, like the join keyword.

The types of joins are used to make a single query to update more than one table at a time. Let’s understand this by updating multiple tables at once.

Consider two tables named library and stu_book tables. And consider the case when a book gets issued from the library to a student.

The count of the books in the library decreases, whereas the count of books with students increases. And that’s the scenario where two separate calls are required.

To avoid separate updates in an RDBMS table, we update the rows in two tables with a single query. Below is the list of statements to be executed before the update call.

List of queries before the actual query on tables:

Query to execute the MySQL statement:

UPDATE library l, stu_book s
    SET l.book_count = l.book_count - 2,
        s.book_count = s.book_count + 2
WHERE l.id = s.book_id;

In the above query, internally, the inner join combines the two tables and operates on the combined table after checking the constraints on the tables. When no keyword gets specified, the inner join gets applied.

Joins like outer join, the right outer join, the user should use the correct keyword. Join can only be performed in the cases when the two tables getting grouped have a similar/matching attribute.

The SET keyword is used along with the UPDATE keyword to set the new values in existing rows. It overrides the older values by writing new data over it.

Here set updates the book count of the stu_book table, and the same number of counts gets decreased from the library book count.

The count does not get updated for all the rows of both tables. Instead, the restriction gets held by the WHERE keyword.

The WHERE keyword does the actual filtration of rows. The keyword filters the row after checking conditions in tables.

Here, this keyword says library_id should match with book_id of the stu_book table.

The output will list the result for the total number of rows affected in both tables. Below is the output that confirms the number of rows affected.

Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Below is the final output for the above query in the local run environment or command prompt.

Local Run Screenshot: