CODEWITHSUNDEEP
sqlsql-server
RJM
RJM

Reputation: 33

Updating column according to index within group

In our databases we have a table called conditions which references a table called attributes.

So it looks like this (ignoring some other columns that aren't relevant to the question)

id attribute_id execution_index
1 1000 1
2 1000 2
3 1000 1
4 2000 1
5 2000 2
6 2000 2

In theory the combination of attribute_id and execution_index should always be unique, but in practice they're not, and the software ends up essentially using the id to decide which comes first between two conditions with the same execution index. We want to add a uniqueness constraint to the table, but before we do that we need to update the execution indexes. So essentially we want to group them by attribute_id, order them by execution_index then id, and give them new execution indexes so that it becomes

id attribute_id execution_index
1 1000 1
2 1000 3
3 1000 2
4 2000 1
5 2000 2
6 2000 3

I'm not sure how to do this without just ordering by attribute_id, execution_index, id and then iterating through incrementing the execution_index by 1 each time and resetting it to be 1 whenever the attribute_id changes. (That would work but it'd be slow and someone is going to have to run this script on several dozen databases so I'd rather it didn't take more than a couple of seconds per database.)

Really I'd like to do something along the lines of

UPDATE c
SET c.execution_index = [this needs to be the index within the group somehow]
FROM condities c 
GROUP BY c.attribute_id
ORDER BY c.execution_index asc, c.id asc

But I don't know how to make that actually work.

Upvotes: 3

Views: 81

Answers (2)

Stu
Stu

Reputation: 32614

It looks like you can use an updatable CTE:

with cte as (
    select *, 
      Row_Number() over(partition by attribute_id order by execution_index, id) new
    from conditions
)
update cte set execution_index = new

I would suggest adding a new column and first updating that and checking the results are as expected.

Example Fiddle

Upvotes: 3

qwertydog
qwertydog

Reputation: 121

WITH cte AS
(
    SELECT
        *,
        ROW_NUMBER() OVER 
        (
            PARTITION BY attribute_id 
            ORDER BY execution_index, id
        ) AS RowNum
    FROM condities
)
UPDATE cte
SET execution_index = RowNum

Upvotes: 3

Related Questions