SQL for Mere Mortals 4th edition - Updating sets of data

Question

I'm going through the book and in Chapter 15 Updating sets of data, in the Problems for you to solve section there is the following problem:

"Set the retail price of accessories (category = 1) to the wholesale price of the highest-priced vendor plus 35 percent"

I'm a bit confused about why the author is giving this solution:

UPDATE Products 
SET 
RetailPrice = ROUND(1.35 * (
                    SELECT DISTINCT WholesalePrice
                    FROM Product_Vendors
                    WHERE Product_Vendors.ProductNumber = 
                          Products.ProductNumber
                        AND WholesalePrice = (
                                SELECT  MAX(WholesalePrice)
                                FROM Product_Vendors
                                WHERE Product_Vendors.ProductNumber = 
                                      Products.ProductNumber)),0)
WHERE RetailPrice < 1.35 * (
                SELECT DISTINCT WholesalePrice
                FROM Product_Vendors
                WHERE Product_Vendors.ProductNumber = Products.ProductNumber
                    AND WholesalePrice = (
                            SELECT MAX(WholesalePrice)
                            FROM Product_Vendors
                            WHERE Product_Vendors.ProductNumber = 
                                  Products.ProductNumber)
                            LIMIT 1)
AND CategoryID = 1;

...instead of:

UPDATE Products 
SET 
RetailPrice = ROUND(1.35 * (SELECT  MAX(WholesalePrice)
                            FROM Product_Vendors
                            WHERE Product_Vendors.ProductNumber = 
                                  Products.ProductNumber),0)
WHERE RetailPrice < 1.35 * (SELECT MAX(WholesalePrice)
                        FROM Product_Vendors
                        WHERE Product_Vendors.ProductNumber = 
                              Products.ProductNumber
                        LIMIT 1)
AND CategoryID = 1;

Both produce the same outcome, the same number of rows being affected in MySQL Workbench ...so why then? Thanks guys.

Sales Orders Modify Schema

[because](https://en.wiktionary.org/wiki/all_roads_lead_to_Rome) — Patrick Artner, Jun 03 '18 at 08:32
Many ways to skin a cat. If you want an even more efficient and concise way, try to express it as an `UPDATE` with an `INNER JOIN`. See https://stackoverflow.com/questions/15209414/how-to-do-3-table-join-in-update-query — Tomalak, Jun 03 '18 at 08:34

score 1 · Accepted Answer · edited Jun 03 '18 at 11:16

It's hard to speculate why the author chose this form. Maybe they wanted to make a point about correlated subqueries. Maybe they wanted to avoid techniques that have not been introduced in the book. Maybe it's the first thing that occurred to them and they never got around to optimizing it. Maybe they wrote the SQL bearing the capabilities of a certain MySQL version in mind (without being 100% sure, I believe there used to be MySQL versions that could not group subqueries).

Here is yet another way of doing it. I would prefer this for its clarity, it's very likely also more efficient.

UPDATE
    Products
    INNER JOIN (
        SELECT   ProductNumber, MAX(WholesalePrice) * 1.35 as RetailPrice
        FROM     Product_Vendors
        GROUP BY ProductNumber 
    ) Target ON Target.ProductNumber = Products.ProductNumber
SET
    Products.RetailPrice = ROUND(Target.RetailPrice, 0)
WHERE
    Products.CategoryID = 1
    AND Products.RetailPrise < Target.RetailPrice;

Thanks Tomalak . There are 3 or 4 more problems in the section with the same kind of solutions where the author seems to be using more code then necessary. A complete mistery to a newb like me. I've spent the last 36h trying to figure why :) Thanks guys for replys — Marius Brad, Jun 03 '18 at 09:16
Don't stress. If you have understood what happens and found a better way on your own, you have very likely exceeded whatever the author had in mind anyway. Enjoy the feeling. ;) — Tomalak, Jun 03 '18 at 09:21

SQL for Mere Mortals 4th edition - Updating sets of data

1 Answers1