Oracle 18g中如何为相同email的记录批量设置统一ID值

问题背景 #

现有数据库表test，表结构定义如下：

CREATE TABLE test(
    id NUMBER(19,0),
    nam VARCHAR2(50) NOT NULL,
    email VARCHAR2(50) NOT NULL
);

表初始数据状态：

需求为：所有email字段值相同的记录，设置完全相同的id值，数据库版本为Oracle 18g，期望实现效果：

实现SQL #

直接使用MERGE语句配合DENSE_RANK()窗口函数即可完成需求，不需要创建额外临时表或写复杂的循环逻辑：

MERGE INTO test t
USING (
    SELECT
        ROWID rid,
        DENSE_RANK() OVER(ORDER BY email) unified_id
    FROM test
) s
ON (t.ROWID = s.rid)
WHEN MATCHED THEN
UPDATE SET t.id = s.unified_id;

说明 #

• DENSE_RANK()窗口函数会按照email字段排序分组，相同邮箱值会返回完全相同的排名序号，天然满足同邮箱同ID的要求，且不同邮箱的ID是连续递增的整数值
• 关联条件使用Oracle内置的ROWID定位单条记录，执行效率最高，不依赖表内现有字段的唯一性
• 如果需要自定义ID的起始值，只需要调整unified_id的计算逻辑即可，比如需要ID从100开始，就把对应行改成DENSE_RANK() OVER(ORDER BY email) + 99 unified_id
• 执行前建议先手动开启事务，执行完语句先校验结果，符合预期再提交，结果不符合直接执行ROLLBACK即可回滚，避免误改数据
• 校验可以直接执行查询语句SELECT * FROM test ORDER BY id, nam;，返回结果和预期效果完全一致

内容的提问来源于stack exchange，提问作者PKS