I am using Oracle database 12C. I have VK_ORDER table which has lot of redundant data with the combination of columns OID and EXID. The table has column FILENAME where the filename is given from which the data are getting inserted in this table and the table has SYSTEM_INSERTED_AT column where on which date time the data has inserted. The daily job is running on this table and redundant data of combination OID and EXID are getting inserted in this table.
I have created another table VK_MODIFY with below columns:
OID
EXID
FILENAME
FIRST_INSERT_DATE
LATEST_MODIFY_DATE
Now i want to write sql statment i am not sure if Merge sql statment works with the below conditions:
If the combination of columns values OID, EXID does not yet exist in the table then :
- Insert a new row
- Set FIRST_INSERT_DATE = LATEST_MODIFY_DATE = now()
- Write the name of the file to FILENAME
If the combination of columns value OID, EXID already exists in the table then:
- set LATEST_MODIFY_DATE = now()
- append the name of the file to FILENAME (prepend a comma – no space)
I can then simply run this query once in a day to update my VK_MODIFY table. I am not sure how to write this sql statment and whether its possible to using oracle sql statement.
`DDL` for `VK_MODIFY` table which has same data type as `VK_ORDER` table.
CREATE TABLE
VK_MODIFY
(
FIRST_INSERT_DATE TIMESTAMP(6) NOT NULL,
LATEST_MODIFY_DATE TIMESTAMP(6) NOT NULL,
FILENAME VARCHAR2(60) NOT NULL,
OID INTEGER,
EXID VARCHAR2(100)
)