This is a question that I run into frequently; I might even have posted it
If I have three tables:
B: product_id, publisher_id, pub_product_id
C: publisher_id, publisher_code
D: product_id, product_price
and I want to find those `pub_products` that are in A, but might or might
not be in B, for publisher `publisher_code`. For those products, I need to
do something to the price. All of the fields, except for `product_price`,
might be used by more than one customer, so I want to filter on publisher. I
know that I can do this with a sub-select:
UPDATE `A` LEFT JOIN
(SELECT B.product_id FROM `C` JOIN B ON C.publisher_id = B.publisher_id
JOIN `D` ON B.product_id = D.product_id
WHERE C.publisher_code = 'Fred'
) AS `X`
ON A.pub_product_id = X.pub_product_id
SET D.product_price = 2 * D.product_price;
Is that the right / best way to handle this?
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341