1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Rep Power

    SQL query driving me nuts

    Please help I have the following problem

    I have a Goals table that has a
    GOAL_id(AI PK) and GOAL_num

    GOAL_num increments for each user ie
    USR_id = 1 (1,2,3,4,5,6)
    USR_id = 2 (1,2,3,4,5,6)

    If I delete say item 3 for user 1 I use this query to re-increment the GROUP_num values
    SET @pos =0;UPDATE goals SET GOAL_num = ( SELECT @pos := @pos +1) WHERE USR_id = 1

    This works GREAT! However
    I have another table called Objectives that has the following fields
    OBJ_id, OBJ_num, GOAL_id, GOAL_num
    If I delete a field in GROUPS I need it to also update the objectives table accordingly. I may nave 100 items in GROUP_num 1, 50 in GROUP_num 2, and 30 in GROUP_num 3 but if I delete GROUP_num 2 I need to make sure all 30 items currently labeled as GROUP_num 3 become 2. I tried this but it didnt work

    SET @pos =0;UPDATE goals SET GOAL_num = ( SELECT @pos := @pos +1) where USR_id = 1 GROUP by GOAL_id

    NOTE: GOAL_id and GOAL_num may not always be the same but all GOAL_num vals that are the same will have the same GOAL_id

    You can seem images at

    keoflex DOT com/clip1.PNG
    keoflex DOT com/num2.PNG
  2. #2
  3. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Rep Power
    Obvious question is why do you need to re-number the items (you also change from Goals to Group in your post!)
    I'd be inclined to say leave things as thay are and in any place where you need a contiguous numbering do so either via the SELECT query or in your application language.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo