CODEWITHSUNDEEP
sqloracle-database
Vinod S P
Vinod S P

Reputation: 23

SQL insert query for avoiding duplicate row inserts

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.

Upvotes: 0

Views: 107

Answers (3)

MT0
MT0

Reputation: 167962

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

You can create an index to prevent multiple insertions with the same CREATED_AT value:

CREATE UNIQUE INDEX my_data__created_at__u ON my_data (created_at);

If you have that, then you can use your query in a PL/SQL statement and catch the exception (and then ignore it, report it or do whatever is required with it):

BEGIN
  INSERT INTO MY_DATA (SITE_ID, USER_NAME, USER_NO, CREATED_AT) 
  VALUES (:siteId, :userName, :userNo, :createdAt);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- Handle the exception
    NULL;
END;
/

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

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

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

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.

Upvotes: 1

Related Questions