MySQL: Update all rows in a table matching results of another query

Question

I've written a query returning rows associating Customers and Salespeoeple.

Note that the query joins several database tables. And note that not all customers have a salesperson.

c_id     c_name   s_id   s_name
  24  microsoft      1     mike
  27        sun      1     mike
  42      apple      2     bill
  44     oracle      1     mike
  47        sgi      1     mike
  58       ebay      2     bill
  61     paypal      3      joe
  65     redhat      1     mike

I also have a single table (called invoices) in my database that looks like this.

i_id   c_id   c_name   s_id   s_name
7208     22   toyota   NULL     NULL
7209     23     ford   NULL     NULL
7210     27      sun   NULL     NULL
7211     42    apple   NULL     NULL
7212     12   nissan   NULL     NULL
7213     15       gm   NULL     NULL
7214     61   paypal   NULL     NULL

How can I use UPDATE in MySQL to make my invoices table look like the table below?

i_id   c_id   c_name   s_id   s_name
7208     22   toyota   NULL     NULL
7209     23     ford   NULL     NULL
7210     27      sun      1     mike
7211     42    apple      2     bill
7212     12   nissan   NULL     NULL
7213     15       gm   NULL     NULL
7214     61   paypal      3      joe

That is to say, how can I update my invoice table to include the correct salesperson_id and salesperson_name, where that relationship exists?

Note that where a Customer/Salesperson relationship exists, all invoices for that customer should have the salesperson associated with it, if there is a salesperson for that customer.

Thanks kindly :-)

Answer 1

Assuming your first table is named customers and those customers without a salesperson have an s_id of NULL

UPDATE invoices JOIN customers USING (c_id)
SET invoices.s_id = customers.s_id, invoices.s_name = customers.s_name
WHERE customers.s_id IS NOT NULL;

I suggest testing in development or running a SELECT query using the JOIN above first to ensure the results.

Answer 2

Using subqueries

Most widely supported option

UPDATE INVOICES
   SET s_id = (SELECT cs.s_id
                 FROM CUSTOMERS_AND_SALES cs
                WHERE cs.c_id = INVOICES.c_id),
       s_name = (SELECT cs.s_name
                   FROM CUSTOMERS_AND_SALES cs
                  WHERE cs.c_id = INVOICES.c_id)
 WHERE INVOICES.c_id IN (SELECT cs.s_id
                           FROM CUSTOMERS_AND_SALES cs)

Using JOINs

UPDATE INVOICES
  JOIN CUSTOMERS_AND_SALES cs ON cs.c_id = INVOICES.c_id
   SET s_id = cs.s_id,
       s_name = cs.s_name