Question 113208 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Order index for rows related to another table

I have a table that stores an id from another table, an index for a specific id and some different value

user_id index_value some_value
1 0 01
1 1 02
1 2 03
2 0 04
3 0 05
3 1 06
1 3 07

I’m about to delete some records and I need to recalculate the data stored in index_value. For example, delete the line with some_value 03.The expected output should look like this:

user_id index_value some_value
1 0 01
1 1 02
2 0 04
3 0 05
3 1 06
1 2 07

What is the best way to go about this?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Used suggestions from Tim Biegeleisen and Akina. After recalculating the indexes, a query is called to remove unnecessary rows.

    UPDATE table AS t
SET index_value =
    (SELECT new_index_value 
    FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY index_value) - 1 AS new_index_value 
        FROM table WHERE user_id IN (
            SELECT user_id FROM table WHERE some_value='some value'
            GROUP BY user_id)
        AND NOT some_value='some value'
        ORDER BY ID)
    WHERE ID = t.ID
)
WHERE user_id IN (
    SELECT user_id 
    FROM table 
    WHERE some_value='some value'
    GROUP BY user_id)
AND NOT some_value='some value';

  2. 0

    I suggest not even maintaining the index_value column, but instead generating this computed value at the time you query. Assuming you are using MySQL 8+:

    SELECT
    user_id,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY some_value) - 1 AS index_value,
    some_value
FROM yourTable
ORDER BY some_value;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search