SQL insert query for avoiding duplicate row inserts-CodePudding

I am using the following SQL insert statement to insert values into a table MY_DATA, but here I want to add a check if the row already exists, based on data in column CREATED_AT.

INSERT INTO MY_DATA (SITE_ID, USER_NAME, USER_NO, CREATED_AT) 
VALUES (:siteId, :userName, :userNo, :createdAt)

Here if I try to insert the row with same value present in CREATED_AT column in the table it should not get inserted.

Using Oracle SQL developer for Database.

Please help I am new here.

CodePudding user response：

I think you only need to create a UNIQUE INDEX on your table on CREATE_AT column -

CREATE UNIQUE INDEX idx_unq_created_at
ON MY_DATA (created_at);

After that you don't need to do anything in your query. This index will take care that no duplicate values are being inserted into created_at column in your table.

CodePudding user response：

One option is to use merge, e.g.

merge into my_data a
  using (select :siteId    site_id, 
                :userName  user_name, 
                :userNo    user_no, 
                :createdAt created_at
         from dual
        ) b
on (a.created_at = b.created_At)
when not matched then 
  insert (site_id, user_name, user_no, created_at)
  values (b.site_Id, b.user_Name, b.user_No, b.created_At);

CodePudding user response：

Use a MERGE statement:

MERGE INTO MY_DATA dst
USING DUAL
ON (dst.created_at = :created_at)
WHEN NOT MATCHED THEN
 INSERT (SITE_ID, USER_NAME, USER_NO, CREATED_AT)
 VALUES (:siteId, :userName, :userNo, :createdAt);