Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 271
Tommy O'Dell Avatar asked Sep 18 '10 18:09

Tommy O'Dell


People also ask

How UPDATE a column value with another column in the same table in MySQL?

In MySQL, if you want to update a column with the value derived from some other column of the same table we can do so by using a SELF JOIN query and if you wish to modify the value derived from another column like maybe get a substring from the text or break the string using some delimiter, then we can use the ...


2 Answers

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
like image 144
OMG Ponies Avatar answered Sep 19 '22 16:09

OMG Ponies


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.

like image 37
Jason McCreary Avatar answered Sep 19 '22 16:09

Jason McCreary