CODEWITHSUNDEEP
javascriptmysqlsqlmariadb
lhuf
lhuf

Reputation: 42

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

Upvotes: 0

Views: 304

Answers (2)

forpas
forpas

Reputation: 164099

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+.

Upvotes: 0

Rob
Rob

Reputation: 12872

Looks like you should have a unique index on course_id,azure_oid That would prevent duplicates from being inserted. Then you can run insert ignore into course_video_viewers... and it will internally drop the record if it exists and reinsert it.

Upvotes: 1

Related Questions