JIYIK CN >

In some cases, users want to update logically related tables at the same time. These logically related tables are linked to each other through some attributes.

Advantages of updating multiple tables in one MySQL query

Similar attributes in the tables are used to create update queries. Update queries perform updates of multiple rows in different tables based on conditions.

When similar situations occur, multiple table update queries are suitable. The benefits of using this type of multi-table update query are:

UPDATEUpdate multiple tables with one query in MySQL using keyword

In a multi-table update query, every record that meets the criteria is updated. Even if the criteria match multiple times, the row is updated only once.

The syntax for updating multiple tables cannot be used with the ORDER BYand LIMITkeywords.

UPDATEThe syntax of the keyword:

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

MULTIPLE UPDATE is not a composite keyword present in MySQL language. The syntax is formed by the combination of various keywords that help in grouping two or more tables, such as joinkeyword.

Join type is used to make a single query to update multiple tables at once. Let us understand this by updating multiple tables at once.

Consider two tables named libraryand stu_book. And consider the case where a book is sent from a library to a student.

The number of books in the library decreases, while the number of books with students increases. This is the scenario that requires two separate calls.

To avoid separate updates in RDBMS tables, we use a single query to update the rows in both the tables. Following is the list of statements to be executed before the update call.

List of queries before the actual query on the table:

Query and execute 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, inner join merges two tables and operates on the merged table after checking the constraints of the tables. When no keyword is specified, inner join is applied.

For joins like outer join, right outer join, user should use right keyword. Join can be performed only if the two tables being grouped have similar/matching attributes.

SETThe keyword UPDATEis used along with the keyword to set new values ​​in an existing row. It overwrites the old values ​​by writing the new data over them.

Here set updates stu_bookthe book quantity of the table and the same amount libraryis decremented from the book quantity.

The counts for all rows of both tables are not updated. Instead, the limit WHEREis preserved by the keyword.

WHEREKeywords do the actual filtering of the rows. Keywords filter the rows after checking the conditions in the table.

Here, this keyword indicates library_idthat should match stu_bookthe table's book_id.

The output will list the results of the total number of rows affected in both tables. Below is the output confirming the number of rows affected.

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

The following is the final output of the above query in the local running environment or command prompt.

Local running screenshots: