使用子查询从多表获取最新EFF_START_TS行并更新旧记录的SQL问题

UPDATE REWARD_ACCT_DETAIL rad
SET rad.EFF_STOP_TS = SYSDATE
WHERE 
-- 匹配指定Client_id对应的奖励账户ID，全用子查询无JOIN
rad.REWARD_ACCOUNT_ID = (
    SELECT reward_account_id FROM 表2
    WHERE subscription_id = (
        SELECT subscription_id FROM 表1
        WHERE Client_id = 37455837
    )
)
-- 排除EFF_START_TS最新的行不更新
AND rad.EFF_START_TS < (
    SELECT MAX(EFF_START_TS) 
    FROM REWARD_ACCT_DETAIL 
    WHERE REWARD_ACCOUNT_ID = rad.REWARD_ACCOUNT_ID
)
-- 仅当该用户对应明细行数超过2条时触发更新
AND EXISTS (
    SELECT 1 
    FROM REWARD_ACCT_DETAIL 
    WHERE REWARD_ACCOUNT_ID = rad.REWARD_ACCOUNT_ID
    HAVING COUNT(*) > 2
);

逻辑说明 #

• 全程未使用JOIN语法，所有关联匹配都通过WHERE子句嵌套子查询实现，符合需求限制
• 内层子查询先通过指定客户ID（示例中为37455837）逐级匹配到对应的奖励账户ID，锁定更新范围
• 子查询获取当前奖励账户下最大的EFF_START_TS，排除最新行，其余历史行全部更新，匹配示例中保留ID为47193932行的要求
• EXISTS判断逻辑保证只有该用户对应的明细行数超过2条时才会执行更新，满足触发条件
• 可直接将代码中的表1、表2替换为实际业务表名，调整Client_id为指定查询的用户ID即可使用

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