如何在不违反唯一约束的情况下执行PostgreSQL更新及统计订阅数

一、修复统计语句错误 #

你原来的统计语句逻辑存在问题，NOT EXISTS子查询没有关联服务ID（iserviceid），导致只要目标用户（13251076）有任何订阅，源用户（13250626）的所有记录都会被排除，根本没检查同一个服务是否在目标用户处存在。

正确的统计语句如下：

SELECT COUNT(*) 
FROM NmsSubscription s1
WHERE s1.iRecipientId = 13250626 
AND NOT EXISTS (
    SELECT 1 
    FROM NmsSubscription s2
    WHERE s2.iRecipientId = 13251076
    AND s2.iserviceid = s1.iserviceid  -- 必须关联服务ID，确保只检查同一服务的订阅情况
);

二、实现订阅分配（仅目标用户未订阅的服务） #

直接执行UPDATE会触发唯一约束报错，因为目标用户已有的服务无法重复设置关联。根据需求分两种场景处理：

场景1：复制订阅（源用户保留订阅，目标用户新增未订阅的服务） #

使用INSERT...SELECT语句，仅插入目标用户未订阅的服务记录：

INSERT INTO NmsSubscription (iserviceid, irecipientid, -- 需列出表中所有非自增、非默认值的必填字段
                             create_time, status -- 示例字段，替换为实际表中存在的字段
                            )
SELECT iserviceid, 13251076, 
       s1.create_time, s1.status -- 复制源用户对应字段的值
FROM NmsSubscription s1
WHERE s1.iRecipientId = 13250626
AND NOT EXISTS (
    SELECT 1 
    FROM NmsSubscription s2
    WHERE s2.iRecipientId = 13251076
    AND s2.iserviceid = s1.iserviceid
);

场景2：转移订阅（源用户删除已分配的订阅，目标用户新增） #

先执行上述INSERT语句完成新增，再删除源用户中已成功转移的订阅：

-- 插入目标用户未订阅的服务
INSERT INTO NmsSubscription (iserviceid, irecipientid, 其他必填字段)
SELECT iserviceid, 13251076, 对应字段值
FROM NmsSubscription s1
WHERE s1.iRecipientId = 13250626
AND NOT EXISTS (
    SELECT 1 
    FROM NmsSubscription s2
    WHERE s2.iRecipientId = 13251076
    AND s2.iserviceid = s1.iserviceid
);

-- 删除源用户中已完成转移的订阅
DELETE FROM NmsSubscription
WHERE iRecipientId = 13250626
AND NOT EXISTS (
    SELECT 1 
    FROM NmsSubscription s2
    WHERE s2.iRecipientId = 13251076
    AND s2.iserviceid = iserviceid
);

关于已更新的10条记录 #

之前UPDATE成功的10条记录，说明这些服务目标用户原本没有，已完成转移。后续执行上述语句时，会自动跳过这些已处理的记录，不会出现重复操作或报错。

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