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

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:

What is the best way to go about this?

CodePudding user response：

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;

CodePudding user response：

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';