CODEWITHSUNDEEP
sqloracle-databasemerge
Symonds
Symonds

Reputation: 194

Insert into table when row not exist else update table using Oracle SQL

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)
)

Upvotes: 0

Views: 716

Answers (2)

Popeye
Popeye

Reputation: 35900

You can use the MERGE query as follows:

MERGE INTO VK_MODIFY T
USING (SELECT * FROM VK_ORDER) S
   ON (T.OID = S.OID AND (T.EXID = S.EXID OR (T.EXID IS NULL AND S.EXID IS NULL)))
WHEN MATCHED THEN
   UPDATE SET T.FILENAME = S.FILENAME || ',' || T.FILENAME,
              T.LATEST_MODIFY_DATE = SYSDATE
WHEN NOT MATCHED THEN
    INSERT(OID, EXID, FILENAME, FIRST_INSERT_DATE, LATEST_MODIFY_DATE) 
     VALUES (S.OID, S.EXID, S.FILENAME, SYSDATE, SYSDATE);

Upvotes: 2

Alistair Wall
Alistair Wall

Reputation: 332

Assuming oid and exid are both not null,

merge into vk_modify M

using (select * from vk_order) O on ((M.OID,M.EXID) = (O.OID,O.EXID)) when matched then update set M.filename=M.filename||','||O.filename, latest_modify_date=sysdate when not matched then insert (oid,exid,filename,first_insert_date,latest_modify_date) values (O.oid,O.exid,O.filename,sysdate,sysdate);

Upvotes: 0

Related Questions