MERGE INTO无法更新ON中的字段解决办法

可以将on里的条件放到update 之后的where条件里

 MERGE INTO xshtest.WEB_USER_VIP T1
        USING (
        select
       53254624 enterpriseId,
        369903 userId,
        1 status,
        4 vipType,
        to_date('2018-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')expireTime,
        11 vipExpireTimeDay
        from dual
     ) T2
        ON ( T1.USER_ID=T2.userId and T1.VIP_TYPE = t2.vipType AND  T1.STATUS =1 )
        WHEN MATCHED THEN
        UPDATE SET T1.enterprise_id = T2.enterpriseId,T1.EXPIRE_TIME = T1.EXPIRE_TIME+T2.vipExpireTimeDay
        where   T1.EXPIRE_TIME>SYSDATE
        WHEN NOT MATCHED THEN
        INSERT (id,user_id,vip_type,expire_time,create_time,source,enterprise_id,status) VALUES(XSHTEST.WEB_USER_VIP_SEQ.Nextval,
        T2.userId,T2.vipType,T2.expireTime,SYSDATE,3,T2.enterpriseId,T2.status);

 

转载于:https://www.cnblogs.com/SimonHu1993/p/10101399.html