如何用SQL UPDATE为COL_A相同值的行设置相同COL_B序列值

问题描述 #

需要为temp表中COL_A值相同的所有行，将COL_B设置为同一个序列值（序列为temp_seq），避免每次更新都生成新的序列值。

准备工作 #

首先创建所需序列：

CREATE SEQUENCE temp_seq START WITH 1 INCREMENT BY 1;

优先方案：SQL MERGE语句（推荐） #

通过先为每个唯一的COL_A分配序列值，再关联更新原表，确保相同COL_A复用同一序列值：

MERGE INTO temp t
USING (
    -- 为每个唯一COL_A生成一次序列值
    SELECT col_a, temp_seq.NEXTVAL AS seq_val
    FROM (SELECT DISTINCT col_a FROM temp)
) s
ON (t.col_a = s.col_a)
WHEN MATCHED THEN UPDATE SET t.col_b = s.seq_val;

备选方案：SQL UPDATE关联子查询 #

同样先为唯一COL_A分配序列值，再通过子查询关联更新：

UPDATE temp t
SET col_b = (
    SELECT seq_val
    FROM (
        SELECT col_a, temp_seq.NEXTVAL AS seq_val
        FROM (SELECT DISTINCT col_a FROM temp)
    ) s
    WHERE s.col_a = t.col_a
);

PL/SQL方案 #

如果需要更灵活的控制，可使用PL/SQL游标遍历唯一COL_A并批量更新：

DECLARE
    CURSOR c_unique_col_a IS
        SELECT DISTINCT col_a FROM temp;
    v_current_seq NUMBER;
BEGIN
    FOR rec IN c_unique_col_a LOOP
        -- 为当前COL_A获取一次序列值
        SELECT temp_seq.NEXTVAL INTO v_current_seq FROM dual;
        -- 更新所有对应COL_A的行
        UPDATE temp SET col_b = v_current_seq WHERE col_a = rec.col_a;
    END LOOP;
    COMMIT;
END;
/

关键说明 #

直接在UPDATE语句中调用temp_seq.NEXTVAL会导致每行生成新值，因为序列的NEXTVAL每调用一次就会递增。上述方案的核心是先为每个唯一的COL_A仅调用一次序列，再将该值批量应用到所有对应行，从而实现相同COL_A复用同一序列值的需求。

验证结果 #

执行上述任一方案后，temp表数据将符合预期：

-------------
COL_A, COL_B
-------------
A, 1  
A, 1
B, 2
C, 3
D, 4    
C, 3
D, 4    
C, 3    

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