Jerry Schwartz wrote:
This is a question that I run into frequently; I might even have posted it before.If I have three tables: A: pub_product_id 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`, are keys. Here’s where I get to wondering: I’m concerned that the same product_id 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?
I would have just added the product_id term to my ON clauses. They can be every bit as complex as a WHERE clause and for LEFT JOINS to act like LEFT JOINS, it's critical to get leave out of the WHERE clause any non-null comparisons for values. Anytime you avoid a dependent subquery your query should also be much faster.
You can also use an INNER JOIN for all of these as you don't need to update any non-matching rows in `A` with 2*NULL.
-- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
