Hi,
I have two tables like: credit_anindya_status and credit_anindya.
In credit_anindya we have records like CARD_ID, CUST_NAME and EXPIRY_DATE having values like 001, ABC, 15-MAY-15 and 002,DEF,20-NOV-20.
I am inserting these datas to credit_anindya_status with the command as:
INSERT INTO credit_anindya_status
(CARD_ID, CUST_NAME, EXPIRY_DATE,STATUS )
SELECT CARD_ID, CUST_NAME, EXPIRY_DATE,'NEW'
FROM credit_anindya
So table credit_anindya_status will have data like: 001, ABC, 15-MAY-15,NEW and 002,DEF,20-NOV-20,NEW.
Now I want to use the condition as (credit_anindya.expiry_date-sysdate)>=1.
So those data will satisfy the condition the status in table credit_anindya_status will get change to "VALID" and those won't satisfy will be "INVALID".
I have used the below merge statement:
merge into credit_anindya_status a
using
(select CARD_ID,CUST_NAME,EXPIRY_DATE from credit_anindya) b
on (a.CARD_ID=b.CARD_ID)
when matched then
update set a.CUST_NAME=b.CUST_NAME,a.EXPIRY_DATE=b.EXPIRY_DATE,a.STATUS='VALID' where (b.expiry_date-sysdate)>=1
when not matched then
insert (a.CARD_ID,a.CUST_NAME,a.EXPIRY_DATE,a.STATUS) values (b.CARD_ID,b.CUST_NAME,b.EXPIRY_DATE,'INVALID');
But the status is remaining as New in table credit_anindya_status.
Kindly help me on this.