How to Update a Table With Data from Another Table in MySQL?

Advanced MySql Query: Update Table with Information from Another Table

Updating a table with data from another table can be a valuable technique in database management. In this instance, we aim to update the "email" column in the "business" table with corresponding data from the "people" table.

The provided query aims to achieve this by matching "business_id" values in both tables and updating only records where the "email" column in the "business" table is empty. However, the query is missing a crucial component: the actual update statement. To correct this, the following query can be employed:

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

This enhanced query correctly sets the "email" column in the "business" table to the corresponding email from the "people" table, considering the specified conditions:

• The "business_id" values in both tables must match.
• Only the first person associated with a particular business ("sort_order" = '1') is considered.
• The update only applies to records in the "business" table where the "email" column is currently empty.

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