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 :-)
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 ...
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)
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With