1

I have a simple table like this:

group | name | price
1     | john |
2     | mike |
3     | paul |
1     | sean |
4     | jack |
2     | brad |
5     | mick |
1     | bill |
4     | chad |

I have two different price values where 100EUR is for a first member of a group and 50EUR is for all additional members of that same group.

Detailed explanation. If a group has only one member, that member gets a price of 100EUR. If a group has multiple members, the first member gets a price of 100EUR, and all additional members of that same group get a price of 50EUR. There can be unlimited number of groups that will be added additionally.

The result should be like this:

group | name | price
1     | john | 100
2     | mike | 100
3     | paul | 100
1     | sean | 50
4     | jack | 100
2     | brad | 50
5     | mick | 100
1     | bill | 50
4     | chad | 50

I'd need a query which would be able to INSERT/UPDATE all missing price fields whenever I manually run it.

Thank you in advance for looking into that matter.

futurion
  • 21
  • 3
  • 1
    How do you determine which member is "first" in each group? Do you have another column that orders them? – Barmar Dec 28 '21 at 22:07
  • Members are added on daily basis, so there's no option to actually sort the table by group id's. Each member can be set to new group or can be attached to existing one. Also, is doesn't matter who is defined as "first" in group. Whichever is found as "first" is ok in my case. I guess the approach below suggested by daviid could do the trick in that case.. – futurion Dec 28 '21 at 22:50

3 Answers3

1

After a lot of trial and error I found a perfect fully functional solution, based on daviid's clever method. The issue with mysql is that by it's structure won't update tables with select methods as subquery. However, self-join (join or inner join) methods can be used instead in this case. I also had to add auto-incremental id to that table, so the final table structure is:

id | group_id | name | price
1  | 1        | john |
2  | 2        | mike |
3  | 3        | paul |
4  | 1        | sean |
5  | 4        | jack |
6  | 2        | brad |
7  | 5        | mick |
8  | 1        | bill |
9  | 4        | chad |

---

SET SQL_SAFE_UPDATES=0;

UPDATE table_name
SET price = 50;

UPDATE table_name AS a
  JOIN 
    ( SELECT id
      FROM table_name
      GROUP BY group_id
      HAVING COUNT(*) >= 1
    ) AS b 
    ON a.id = b.id
SET a.price = 100;

Thanks also to Cody and Barmar for usable hints...

futurion
  • 21
  • 3
0

A partial answer: you can GROUP BY your "group" field and tack on a HAVING COUNT(group) > 1 to determine if that group has more than 1 member.

That is, to see all groups with more than one member it would look like:

SELECT
group
FROM table
GROUP BY group
HAVING COUNT(group) > 1

That will just tell you which groups have multiple members. Without another way to ensure ordering you cannot tell which member is "first" in their group and thus should be priced at 100 and all others priced at 50.

Cody Caughlan
  • 32,456
  • 5
  • 63
  • 68
  • Thank you for this suggestion, I actually found that piece of code and already tried to use it before, but as you said, it only shows duplicates, but the issue remains. As said above, in my case it actually doesn't matter which member is defined as "first". It's only important one gest full price, all others get half price. – futurion Dec 28 '21 at 22:52
0

The following queries are not tested and might contain syntax errors. But they are good enough to understand the principle. There are many possible ways to achieve your result.

Here is my take: I would make use of one query to UPDATE the price on every row and set it to 50 whether it is the first group member or not. >table_name<, of course, needs to be changed to the name of your mentioned table.

UPDATE >table_name<
SET price = 50;

Then I would take care of each individual group and the respective first member by running the following query. Adapt the query to each group by changing the >groupId<.

UPDATE >table_name<
SET price = 100
WHERE id = (
    SELECT id
    FROM >table_name< 
    WHERE group = >groupId< 
    ORDER BY id
    LIMIT 1
);

Take a look a the nested query: It queries the table for all members of only one group, orders them in ascending order and only returns an id per member. By applying LIMIT to the query, the result will just be the first group member's id. The resulting id can then be used in the other query to update the price and set it to 100.

But be careful: If you insert/delete (new) members with an id that is not just counting up, this query might select a "new first member".

David
  • 184
  • 8
  • Thank you for this suggestion, I never thought of it really. I was thinking about some kind of recursion or aggregation in this case, but it seemed way too complicated. It seems like a really clever approach. I have to edit my query in production table and see if it works right. Maybe just one question though. Can you elaborate in which particular case this query might find a new "first" member and fail? Actually in my case, it's not important who is found as "first" member. It only matters that only one member of same group gets full price, all others get half price. – futurion Dec 28 '21 at 22:56
  • I tried the suggested query, but it seems this syntax is not allowed in mysql as it returns an error "You can't specify target table 'table_name' for update in FROM clause". Do you maybe have any other suggestion how to overcome that limitation? – futurion Dec 28 '21 at 23:16
  • Do not test these queries in your production database! Make sure they actually work in a temporary table. Regarding your question: Always make sure to run all queries. If you just run the "first type query" once and decide to update the list with only the "second type query", you might find two or more people being the first group member. There is no problem if your ids are not recycled. If they are, you might not get consistent results. Imagine the current first group member has ID 55. You then insert a new member with ID 2. This memeber will then be the "new first" (old != new "first"). – David Dec 28 '21 at 23:18
  • Yes, this is true, our members are never recycled, once they get an "group" id, it stays with them forever, we don't delete them, we only add new groups or we join new members to existing groups. However, the issue still remains as I commented above. It seems mysql won't allow to UPDATE table in such way. Is there maybe an alternative approach possible? – futurion Dec 28 '21 at 23:30
  • Maybe you can overcome that limitation by chaining both queries with the IN operator? Or if this does not work either, try copying the resulting id into a new temporary table. And then query the id from there? – David Dec 28 '21 at 23:32
  • See https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql?rq=1 for how to do a self-join to get the min or max row in a group. You can use this to assign the 100 price to that row. – Barmar Dec 28 '21 at 23:40
  • It seems the suggested solution only works as a single "select" query, but it won't work as a sub-query to inside "update" statement. I believe I'm doing something wrong, but all operands return same error as mentioned above. Is there a chance for working example maybe? – futurion Dec 29 '21 at 00:09
  • Please see above my solution. The function daviid provided does work good "in theory", but mysql doesn't support updates with nested select queries. The solution to this is using join or inner join functions using actually same logic, just with different approach... Thank you again for usable hints. – futurion Dec 29 '21 at 12:48