Given the following table structure in a table people:

id (int, PK)	name	group_id	net_worth (float)	is_leader (bool)
1	John	1	5000.0	1
2	Mike	1	8000.0	0
3	Paul	2	3000.0	0
4	Logan	2	5000 .0	0

I want to update is_leader so that each group_id has only one entry that has is_leader = 1 (only 1 leader per group), the leader of a group is determined by whoever has the highest net_worth value.

The condition however is to not update a group if it already has a leader (determined by is_leader = 1)

How can I fetch all members of groups that don't already have leaders, and from the resultset determine who will be the leader based on highest net_worth?

My Attempt

So far I am only able to fetch the person with the highest net_worth for each group, however it still fetches groups that already have a leader.

SELECT `id`, `name`, MAX(`net_worth`)
FROM `people`
WHERE `is_leader` = 0
GROUP BY `group_id`
ORDER BY `name` ASC;

Expected Output

id (int, PK)	name	group_id	net_worth (float)	is_leader (bool)
1	John	1	5000.0	1
2	Mike	1	8000.0	0
3	Paul	2	3000.0	0
4	Logan	2	5000 .0	1

• Group 1 is not affected because it already has an appointed leader, even though another member has a higher net_worth.
• Group 2 was appointed a new leader (ID 4) because they had the highest net_worth amongst any other member in the group, and this group didn't already have someone appointed as leader.

DBFiddle: db-fiddle.com/f/rH3TSBHBoNa9hthDKpBqEg/2

CodePudding user response：

You want to update rows if they are the highest net_worth (so there is no other row with a higher net worth) for the group and if the group does not already have a leader.

This translates easily into two left joins that must find no records:

UPDATE `people` p1
LEFT JOIN `people` p2 on p2.group_id = p1.group_id AND (p2.net_worth,p2.id) > (p1.net_worth,p1.id)
LEFT JOIN `people` p3 on p3.group_id = p1.group_id AND p3.is_leader
SET p1.is_leader = 1
WHERE p2.id IS NULL AND p3.id IS NULL;

Here I chose to break ties on net_worth by which id is higher. To set is_leader on the lowest tied id instead, do:

and (p2.net_worth > p1.net_worth or p2.net_worth=p1.net_worth and p2.id < p1.id)

To check some additional tie-breaking field before id, using or begins to get cumbersome so I prefer:

and coalesce(
    if(p2.net_worth=p1.net_worth,null,p2.net_worth > p1.net_worth),
    if(p2.tiebreaker=p1.tiebreaker,null,p2.tiebreaker > p1.tiebreaker),
    p2.id < p1.id  -- or change < to >
)

CodePudding user response：

Using an update join:

UPDATE people p1
INNER JOIN
(
    SELECT group_id, MAX(net_worth) AS max_net_worth,
           SUM(is_leader) AS leader_cnt
    FROM people
    GROUP BY group_id
) p2
    ON p2.group_id = p1.group_id AND
       p2.max_net_worth = p1.net_worth
SET
    is_leader = 1
WHERE
    p2.leader_cnt = 0;

CodePudding user response：

In the UPDATE statement you can join the table to a query that returns the max net_worth and max is_leader of each group_id.
The max net_worth value will be used to determine which one will be the leader and the max is_leader value will be used to filter out the group_ids that already have a leader:

UPDATE people p
INNER JOIN (
  SELECT group_id, MAX(net_worth) net_worth, MAX(is_leader) is_leader
  FROM people
  GROUP BY group_id
) t ON t.group_id = p.group_id AND t.net_worth = p.net_worth AND t.is_leader = 0
SET p.is_leader = 1;

See the demo.