How to Update a Table with Data from Another Table Based on Matching IDs and Sort Order?

Advanced MySQL Query: Updating a Table using Data from Another Table

The task at hand is to update the email column in the business table with data from the email column in the people table. The two tables are connected by the business_id column.

A straightforward approach to this update query is as follows:

<code class="sql">UPDATE business b
SET b.email = (
    SELECT email
    FROM people p
    WHERE p.business_id = b.business_id AND sort_order = '1'
)
WHERE b.email = '';</code>

However, this query would only update the very first match for each business_id in the people table. The modification to ensure we update all relevant business records is:

<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>

By joining the business (b) and people (p) tables explicitly using the JOIN syntax, the query ensures that only people with the highest sort_order value (i.e., '1' in this case) affect the update. This more accurately matches the intended goal of updating the business data with the email of the highest-ranked person for each business.

The above is the detailed content of How to Update a Table with Data from Another Table Based on Matching IDs and Sort Order?. For more information, please follow other related articles on the PHP Chinese website!