0

Say I have a table for "users" consisting of "group_id" and "group_rank"

I have a table for all groups and their group information, including ID.

I have a support table for all ranks for all groups, with keys as "group_id" and "sort_id" (sort_id so I can swap a rank up and down.) I do not have the luxury of just referring to the ranks unique id reference key as sort_id only has this many values it can support (1 through 50)

I realize the most efficient way to solve the issue would be to have users "group_rank" refer to the rank support tables unique ID, however I don't have that luxury unfortunately, so it has to refer to "sort_id" based on "group_id" from both rank/users table.

Now, tricky part, I want to swap two ranks' sort position. I've done that efficiently enough with a swap query ( how to update swap values of two rows with single query )

However, I want to do the same for all "users" affected. Is there any easier way than fetching all users with group_id match and process through them then create 2 new queries to update for their separate new rank_id's ?

Community
  • 1
  • 1
Flying Turtles
  • 1
  • 1
  • Welcome at SO. You could enhance your question by providing sample data and the desired result, so anybody interested in your question can understand easily your intention. This will help you getting good answers. – VMai Jul 16 '14 at 22:39
  • Are you after something like this? http://sqlfiddle.com/#!6/0ca3a/2 – JohnLBevan Jul 16 '14 at 22:41

1 Answers1

0

Thank you JohnLBevan, I was indeed looking for CASE, didn't think through what I really wanted with my query and overcomplicated it in my head. Appreciate your assistance.

For reference (example parameters for future use);

UPDATE `users` SET `rank_id` = CASE  WHEN `rank_id` = '1' THEN 2 WHEN `rank_id` = '2' THEN 1 ELSE `rank_id` END WHERE `group_id` = '43'
Flying Turtles
  • 1
  • 1