Hi - In one of our Oracle target(11 G) table where the records are being inserted instead of update with existing record based on one column value which is being referenced from some other table which has unique IDs for the same set of records. But in my target table i want to update with the existing records(suppose if it's have 2 records i need to update the old records with new record column values and delete the new one.
Target table metadata-
TGT_ID(PK)
TGT_POL_ID(PK)
TGT_PC_ID
TGT_POL_NUM
TGT_PC_CODE
TGT_SRV_MIL
TGT_COUP_VAL
TGT_AVAI_FLAG
TGT_SRV_TYPE
TGT_STATUS
TGT_TERM_DATE
TGT_CLM_NUM
TGT_CREATED_DATE
TGT_CREATED_BY
TGT_LAST_UDPATED_DATE
TGT_LAST_UDPATED_BY
here the combination of TGT_POL_NUM and TGT_PC_CODE should be unique for each record and new record is being inserted based on other column (GT_PC_ID) value which is being referenced from other table. Here i's like to update the old record for the unique combination records of TGT_POL_NUM and TGT_PC_CODE from the new record only with these column values TGT_STATUS,TGT_TERM_DATE,TGT_LAST_UDPATED_DATE,TGT_LAST_UDPATED_BY and delete the newer one.
We have almost 2 billion record in our target table and 1800 duplicate records among them. Can you help me out with a Stored procedure to address this?