Asher11 Asher11 - 11 months ago 37
MySQL Question

fill groups with values based on group values (update with group bys) - SQL - postgre

I have the situation in which I produce some output, and based on the gorup of belonging, it gets and index (common for the group). then I the production goes on and I insert some new values that belong to the very same group. I want to assign to these new records the same "marker" that the group already had. I'd like to do it with

as detailed in the picture below, the groups are composed by a tuple (continent, zone, state) which I, in fact, group by. (table name: '

enter image description here

How can I put the number 5 aside manchester and 6 aside Lyon?

Answer Source

If you are OK with first inserting the data, and then running a second query to update the blank group numbers, then there is one option:

UPDATE yourTable t1
SET group =
    SELECT continent, zone, state
           MAX(group) AS group
    FROM yourTable
    GROUP BY continent, zone, state
) t2
WHERE t1.continent = t2.continent AND      =      AND
      t1.state     = t2.state

The inner query in the FROM clause finds the maximum group value for each continent, zone, state combination. There should only be two types of values for each group combination, namely a number and/or a NULL. The NULL value would indicate that the record were newly inserted, but it would be ignored by the MAX function.