SQL Server从RandomPassword表取随机值更新客户表密码字段方法问询

实现逻辑 #

通过CTE分别为待更新的无密码客户记录、密码库表生成随机排序的关联行号，保证单次更新中每个客户只会匹配到唯一一条密码记录，天然避免两个密码字段关联不同记录的问题。

实现代码 #

BEGIN TRANSACTION;

WITH 待更新客户 AS (
    -- 筛选所有未设置密码的客户，生成随机排序的行号
    SELECT 
        IDCustomer,
        PasswordID,
        PasswordEncrypt,
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS 行号
    FROM 客户表
    WHERE PasswordID IS NULL AND PasswordEncrypt IS NULL
),
随机密码库 AS (
    SELECT 
        ID AS 密码ID,
        PasswordEncrypt,
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS 行号,
        COUNT(*) OVER () AS 密码总条数
    FROM RandomPassword
)
UPDATE c
SET 
    c.PasswordID = p.密码ID,
    c.PasswordEncrypt = p.PasswordEncrypt
FROM 待更新客户 c
-- 客户行号对密码总条数取模，支持客户数超过密码库总数量的场景，循环取用密码
INNER JOIN 随机密码库 p ON p.行号 = (c.行号 - 1) % p.密码总条数 + 1;

COMMIT TRANSACTION;

相关说明 #

• 采用NEWID()作为随机排序依据，解决了RAND()函数全局仅生成一次随机值、无法实现逐行随机的问题
• 关联逻辑保证每次更新的PasswordID和PasswordEncrypt必然来自同一条RandomPassword记录，不会出现字段不匹配的情况
• 内置取模逻辑支持客户数量超过密码库总条数的场景，可循环复用密码库记录
• 外层包裹事务，更新出现异常可直接回滚，符合单事务操作要求
• 代码中客户表请替换为你实际使用的客户表表名

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