MySQL updating all records having max value within group

Question

Apologies if this has been answered elsewhere and I'm just not seeing it; this is the closest I've found, but it isn't quite what I'm trying to do.

MySQL - updating all records to match max value in group

I have a table on a production web server with about 15,000 rows. There are many records sharing an item_name with another record, but item_meters is (normally, but not always) a unique value for each row. item_id is always unique. Currently every record has a value of "0" in the item_flag column.

I would like to update all records with the largest item_meters value within each item_name group to have an item_flag value of "1".

Here is a simplified version of the table ordered by item_id ASC:

----------------------------------------------
                  mytable
----------------------------------------------
item_id | item_name | item_meters | item_flag
--------+-----------+-------------+-----------
001     | aaa       | 224         | 0
002     | aaa       | 359         | 0
003     | aaa       | 456         | 0
004     | bbb       | 489         | 0
005     | bbb       | 327         | 0
006     | bbb       | 215         | 0
007     | ccc       | 208         | 0
008     | ccc       | 756         | 0
009     | ccc       | 756         | 0
--------+-----------+-------------+-----------

The desired result would be a table with "1" in the item_flag column for each "aaa" having the largest item_meters, each "bbb" having the largest item_meters, each "ccc" having the largest item_meters, etc. like this:

----------------------------------------------
                  mytable
----------------------------------------------
item_id | item_name | item_meters | item_flag
--------+-----------+-------------+-----------
001     | aaa       | 224         | 0
002     | aaa       | 359         | 0
003     | aaa       | 456         | 1
004     | bbb       | 489         | 1
005     | bbb       | 327         | 0
006     | bbb       | 215         | 0
007     | ccc       | 208         | 0
008     | ccc       | 756         | 1
009     | ccc       | 756         | 0
--------+-----------+-------------+-----------

(In case there are 2 or more records having the same item_name and the same item_meters (e.g. item_id 008 and 009 above), the desired result would be for the record with the numerically lower item_id (item_id is always unique), to have an item_flag value of "1" while the row with a numerically higher item_id would still have an item_flag value of "0")

Also of note, even though this database is running behind a production web server with new rows added every day, there will be no need to update the table every time a new row is added. It is something that will only be required once, regardless of whether new rows are later added outside of the parameters. The reason I mention this, is because execution speed is not a big concern since the query will only be executed once.

Thank you in advance! Please let me know if I can provide more info or clarify my question in any way.

Have you came up with your `query`? can you show us your code? — Luzan Baral, Jan 30 '17 at 07:13
Hello @reds, thank you for taking the time to reply. I'm sorry if I'm misunderstanding. The expected result would be the second table example given, which shows the desired rows with item_flag = "1" — Rick Irwin, Jan 30 '17 at 07:17
Thank you @Luzan Baral, I have thus far only managed queries to select, not update the desired rows. Everything else I have tried has been throwing darts — Rick Irwin, Jan 30 '17 at 07:19
You mean all item_flag = 1 is that you need to be an output? The desired result would be a table with "1" in the item_flag — Vijunav Vastivch, Jan 30 '17 at 07:22

spencer7593 · Accepted Answer · 2017-02-02T15:45:26.863

The approach I take is to first write a query (SELECT statement) that will return the item_id values of the rows we want to update.

As a starting point, get the maximum value for item_meters, a simple query like this:

  SELECT m.item_name
       , MAX(m.item_meters) AS max_item_meters
    FROM my_table m
   GROUP BY m.item_name

We can use that query as an inline view in another query, to get the lowest item_id for each of those item_name

  SELECT MIN(o.item_id) AS min_item_id
    FROM ( SELECT m.item_name
                , MAX(m.item_meters) AS max_item_meters
            FROM my_table m
           GROUP BY m.item_name
         ) n
    JOIN my_table o
      ON o.item_name = n.item_name
     AND o.item_meters = n.max_item_meters
   GROUP BY o.item_name, o.item_meters

And we can use that query as an inline view that gets the whole row associated with the item_id values we returned...

  SELECT t.item_id
       , t.item_name
       , t.item_meters
       , t.item_flag
    FROM my_table t
    JOIN ( SELECT p.min_item_id
             FROM ( SELECT MIN(o.item_id) AS min_item_id
                      FROM ( SELECT m.item_name
                                  , MAX(m.item_meters) AS max_item_meters
                               FROM my_table m
                              GROUP BY m.item_name
                           ) n
                      JOIN my_table o
                        ON o.item_name = n.item_name
                       AND o.item_meters = n.max_item_meters
                     GROUP BY o.item_name, o.item_meters
                  ) p
         ) q
      ON q.min_item_id = t.item_id

Once the SELECT query is working, convert that to an UPDATE statement... replace the SELECT ... FROM with UPDATE, and add a SET clause. (Sometimes, it's necessary to wrap the inline view in yet another SELECT, to avoid MySQL error about disallowing references to the table we are updating.)

  UPDATE my_table t
    JOIN ( SELECT p.min_item_id
             FROM ( SELECT MIN(o.item_id) AS min_item_id
                      FROM ( SELECT m.item_name
                                  , MAX(m.item_meters) AS max_item_meters
                               FROM my_table m
                              GROUP BY m.item_name
                           ) n
                      JOIN my_table o
                        ON o.item_name = n.item_name
                       AND o.item_meters = n.max_item_meters
                     GROUP BY o.item_name, o.item_meters
                  ) p
         ) q
      ON q.min_item_id = t.item_id
     SET t.item_flag = '1'

If the intent is not to UPDATE the existing table, but to return a resultset, we can write a query and do an outer join to that same inline view, and return either a 0 or a 1 for item_flag, testing whether the item_id matches one we want flagged as 1...

  SELECT t.item_id
       , t.item_name
       , t.item_meters
       , IF(q.min_item_id IS NULL,0,1) AS `item_flag`
    FROM my_table t
    LEFT
    JOIN ( SELECT p.min_item_id
             FROM ( SELECT MIN(o.item_id) AS min_item_id
                      FROM ( SELECT m.item_name
                                  , MAX(m.item_meters) AS max_item_meters
                               FROM my_table m
                              GROUP BY m.item_name
                           ) n
                      JOIN my_table o
                        ON o.item_name = n.item_name
                       AND o.item_meters = n.max_item_meters
                     GROUP BY o.item_name, o.item_meters
                  ) p
         ) q
      ON q.min_item_id = t.item_id

Hello and thank you very much. I will try this and report back! — Rick Irwin, Jan 31 '17 at 16:36
I'm getting this error from the 3rd select query: Error in query (1054): Unknown column 'o.max_item_meters' in 'on clause' — Rick Irwin, Feb 02 '17 at 12:46
that should be **`=n.max_item_meters`**, and on the line above that, should be **`=n.item_name`**. my bad. — spencer7593, Feb 02 '17 at 15:44
Aha Brilliant! It now works exactly as expected. Thank you very much! — Rick Irwin, Feb 04 '17 at 00:30

MySQL updating all records having max value within group

1 Answers1