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.
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;
/
ON clause ON (dst.created_at = :created_at AND dst.user_no = :userNo) or for the index CREATE UNIQUE INDEX my_data__created_at__u ON my_data (user_no, created_at);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.
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);
