How to Update a MySQL Table with Data from Multiple Sources?

Advanced MySQL Query: Updating Tables with Data from Multiple Sources

Consider a scenario where you need to update a table in MySQL with data from another related table. To achieve this, you can leverage the power of SQL joins. By utilizing this technique, it is possible to seamlessly merge information from various tables, allowing you to perform complex data manipulation tasks.

In this specific case, you seek to update the 'email' column in the 'business' table with the corresponding 'email' values from the 'people' table. The connection between the two tables is established through the 'business_id' column.

To accomplish this task effectively, you can employ the following SQL query:

<code class="sql">UPDATE business b, people p
SET b.email = p.email
WHERE b.business_id = p.business_id
AND p.sort_order = '1'
AND b.email = ''</code>

This query employs a table join to connect the 'business' (b) and 'people' (p) tables based on the common 'business_id' column. The 'sort_order = '1'' condition ensures that only the email address with the lowest sort order is used to update the 'business' table. Additionally, the query only updates rows where the 'email' column in the 'business' table is empty ('').

By executing this query, you will successfully update the 'email' column in the 'business' table with the appropriate email addresses from the 'people' table, allowing you to maintain data consistency and integrity across multiple tables.

The above is the detailed content of How to Update a MySQL Table with Data from Multiple Sources?. For more information, please follow other related articles on the PHP Chinese website!