Update each group's property with the lowest element's property of the corresponding group

Question

I have data like

Old table

acc|name|group|priority
1  |aa  |g1   |1
2  |ff  |g1   |2
3  |ss  |g1   |2
4  |gg  |g2   |3
5  |uu  |g2   |4
6  |dd  |g2   |4
.  |.   |.    |.
xx |xx  |xx   |x
 

I want to update name to new table with sql to

New table

acc|name|group|priority
1  |aa  |g1   |1
2  |aa  |g1   |2
3  |aa  |g1   |2
4  |gg  |g2   |3
5  |gg  |g2   |4
6  |gg  |g2   |4
.  |.   |.    |.
xx |xx  |xx   |x
 

In table, name in group g1 must update to aa with minimum priority in group g1. Do you have any idea to update all group with name in minimum priority?

Answer 1

I expect you would try the solution on your own effort first.

Initially I am sharing the idea:

1. First you need to know the name of entry of each group which holds the minimum priority. In order to know the minimum row for each group just google it. This article might be useful.
2. Then you need to make an inner join between your main table and the table you've just got from step 1 on matching groups and then update each row with the name of the row which holds priority greater than the minimum priority of the corresponding group.

Finally come up with a solution of your own and check with the solution I am giving right below:

UPDATE your_table YT 
INNER JOIN 
(
SELECT 
A.*
FROM your_table A 
INNER JOIN 
(
    SELECT 
    `group` AS group_name,
    min(priority) AS min_priority
    FROM your_table
    GROUP BY `group`
) AS B 
ON A.`group` = B.group_name
AND A.priority = B.min_priority
) AS helper 
ON YT.`group` = helper.`group`
SET YT.name = helper.name 
WHERE YT.priority > helper.priority;

---

See Demo

---

Test schema:

create table your_table (
    acc int, 
    name varchar(30), 
    `group` varchar(30), 
    priority int
);


insert into your_table values(1,'aa','g1',1);
insert into your_table values(2,'ff','g1',2);
insert into your_table values(3,'ss','g1',2);
insert into your_table values(4,'gg','g2',3);
insert into your_table values(5,'uu','g2',4);
insert into your_table values(6,'dd','g2',4);

Caution: group is a reserved word in mysql. Whenever you name your identifier by any of the reserved words you need to use backtick (`) ...Read more