How to Update MySQL Records Using JOIN Operations?

Joining Tables to Update MySQL Records

When dealing with multiple tables in a database, it's often necessary to update records based on values in another table. This article demonstrates how to perform such updates in MySQL using a single query.

Consider the following scenario: you have two tables, one with SUBST_ID values and CREATED_ID values, and the other with SERIAL_ID values and empty BRANCH_ID values. You want to update the BRANCH_ID column in the second table with the corresponding CREATED_ID values from the first table.

To achieve this, you can use a join operation between the two tables on the matching SERIAL_ID and SUBST_ID columns. The following query illustrates the solution:

UPDATE TABLE2
JOIN TABLE1
ON TABLE2.SERIAL_ID = TABLE1.SUBST_ID
SET TABLE2.BRANCH_ID = TABLE1.CREATED_ID;

In this query, the UPDATE statement updates the TABLE2 table, joining it with the TABLE1 table on the condition that SERIAL_ID and SUBST_ID match. The SET clause sets the BRANCH_ID column in TABLE2 to the matching CREATED_ID column in TABLE1.

By executing this query, you will successfully update all rows in TABLE2 with the appropriate CREATED_ID values from TABLE1, ensuring data consistency between the two tables.

The above is the detailed content of How to Update MySQL Records Using JOIN Operations?. For more information, please follow other related articles on the PHP Chinese website!