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

Viewed 28673

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

3 Answers
Related