How to conditionally insert into mariadb based on if the entry already exists

I have the following:

Table with ID, title, userid

At the moment I have logic that when a certain media is viewed it is entered into the database in order to store view history.

Now, I am trying to prevent duplicate entries from being inserted. This is the code that I have tried and it is still duplicating entries.

    dataAccessor.viewers = {
  add: ({ courseId, viewerOid }) => {
    const query =
      "IF NOT EXISTS (SELECT * FROM course_video_viewers WHERE course_id = ? AND WHERE azure_oid = ?) INSERT INTO course_video_viewers (course_id, azure_oid) VALUES (?, ?)";

    const inputs = [courseId, viewerOid];

    return sendQueryAndReturnResultsAsPromise(query, inputs);
  }
};

Answer

The best thing to do is to add a unique constraint in the table for the combination of the columns course_id and azure_oid:

ALTER TABLE course_video_viewers 
ADD CONSTRAINT un_con_video_view 
UNIQUE (course_id, azure_oid);

If you can’t alter the table’s definition you can use a INSERT ... SELECT statement:

INSERT INTO course_video_viewers (course_id, azure_oid) 
SELECT ?, ?
FROM dual
WHERE NOT EXISTS (SELECT * FROM course_video_viewers WHERE course_id = ? AND WHERE azure_oid = ?)

You may omit FROM dual if your version of MySql is 5.7+.

Leave a Reply

Your email address will not be published. Required fields are marked *