I have a table where records are inserted and updated. In case of updates, a new row is inserted into the table. In order to track updates for a given record, there's a column added to the table called root_record_id which holds the id of the very first record in the update chain.

For eg: Consider the record table schema as follows:

In this case, a record with id=1 was inserted, which was then updated to id=3 and then to id=4. Similarly the record with id=2 was inserted and then updated to id=5.

I want to add a version column to this table, where version is incremented on each update and starts with 0.

I tried writing queries using group by clause on root_record_id but failed to accomplish the task.

CodePudding user response：

DBFIDDLE

This query produces the version that you can use (in an update, or in a trigger):

SELECT
    id,
    root_record_id,
    RANK() OVER (partition by root_record_id ORDER BY id ASC)-1 version
FROM table1
ORDER BY id;

output:

CodePudding user response：

If you are looking for the general sequence on how to add the column and then pre-fill the values, then follow this fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5a04b49fbda3883a9605f5482e252a1b

1. Add the version column allowing nulls:

   ALTER TABLE Records ADD version int null;
   

2. Update the version according to your logic:

   UPDATE Records
   SET version = lkp.version
   FROM Records r
   INNER JOIN (
       SELECT Id, COUNT(root_record_id) OVER (partition by root_record_id ORDER BY id ASC)-1 as version
       FROM Records
   ) lkp ON r.Id = lkp.Id;
   

3. Alter the version column to NOT allow nulls

   ALTER TABLE Records ALTER COLUMN version int not null;
   

4. Finally, ensure that you increment the version column during new row inserts.