将显式版本分配给表的现有行

Question

我有一个表，在其中插入和更新记录。在进行更新时，会在表中插入一个新行。为了跟踪给定记录的更新，在表中添加了一个名为root_record_id的列，该列保存更新链中第一条记录的id。

例如：考虑如下记录表模式：

id	root_record_id	其他列
1	1
2	2
3	1
4	1
5	2

如果您正在寻找如何添加列并预填充值的一般顺序，请遵循以下步骤：https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5a04b49fbda3883a9605f5482e22a1b

1. 添加允许为null的version列：

   ALTER TABLE Records ADD version int null;
   

2. 根据您的逻辑更新version：

   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. 将version列更改为NOT允许空

   ALTER TABLE Records ALTER COLUMN version int not null;
   

4. 最后，确保在插入新行期间增加版本列。

DBFIDDLE

此查询生成版本，您可以使用(在更新或触发器中(：

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

输出：

<1>

id	root_record_id	版本
1	1	0
2	2	0
3	1
4	1	2
5	2	1