I would like to update a table in mySql with data from another table.
I have two tables "people" and "business". The people table is linked to the business table by a column called "business_id".
The necessary table structure, primary key is starred (Table: columns): People: *business_id, *sort_order, email Business: *business_id, email
I would like to update the business table email column with the email from the people table, something like this (I know I am missing something here):
UPDATE business b SET email = (SELECT email from People p where p.business_id = b.business_id AND sort_order = '1') WHERE b.email = '';
Does this make sense? Is it possible?
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 = ''
Note, if sort_order is an INT, then don't use '1' - use 1:
UPDATE business b JOIN People p ON p.business_id = b.business_id AND p.sort_order = '1' SET b.email = p.email WHERE b.email = '';