Guide to inserting data into mariadb only if the entry does not already exist

Question

Guide to inserting data into mariadb only if the entry does not already exist

I am faced with the challenge of avoiding duplicate entries in a table that contains ID, title, and userid fields. Currently, I have implemented logic to store view history in the database when certain media is viewed.

However, despite my efforts, duplicate entries are still occurring even after trying the following code:

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

javascript mysql sql mariadb

Answer 1

Answer №1

To avoid duplicate entries, consider adding a unique index on course_id and azure_oid. This will prevent the same data from being inserted multiple times. You can then use the SQL command

INSERT IGNORE INTO course_video_viewers...

to handle inserting records efficiently by dropping and reinserting if necessary.

Answer 2

To avoid duplicate entries, consider adding a unique index on course_id and azure_oid. This will prevent the same data from being inserted multiple times. You can then use the SQL command

INSERT IGNORE INTO course_video_viewers...

to handle inserting records efficiently by dropping and reinserting if necessary.

Answer 3

Answer №2

To ensure uniqueness in the table, it is recommended to add a unique constraint for both the course_id and azure_oid columns:

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

If modifying the table's definition is not an option, you can achieve the same result using an 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 = ?)

The use of FROM dual may be omitted for MySql versions 5.7 and above.

Answer 4

To ensure uniqueness in the table, it is recommended to add a unique constraint for both the course_id and azure_oid columns:

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

If modifying the table's definition is not an option, you can achieve the same result using an 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 = ?)

The use of FROM dual may be omitted for MySql versions 5.7 and above.

Guide to inserting data into mariadb only if the entry does not already exist

Answer №1

Answer №2

Similar questions

Error: An unexpected 'if' token was not caught

What is the most efficient way to transform HTML into React components effortlessly?

Incorporating dynamic images into Laravel using Angular

Switch out multiline text with javascript

Tips for utilizing props in a Vue component

Is there a method to send a function through a remix loader function?

Currently, I am experimenting with creating interactive tabs by utilizing jQuery

Prevent expand/collapse action on list item checkbox in jQuery Mobile

How can I designate the file name when using Ajax to export in Excel formatting?

Has anyone successfully utilized pgloader for transferring data from Mysql to Postgres during a migration process?

What are the steps for translating multiple meshes in various directions using three.js?

"Keep a new tab open at all times, even when submitting a form in

What could be causing the lack of change in a dynamic input value in Angularjs?

The communication issue between the two devices has occurred due to a missing shared object file, specifically, the libmariadb.so.3 file cannot be opened

Arranging an array of objects in Javascript based on the first attribute, and in cases of equality, sorting with another attribute

Using Mongoose to Add Documents

Click on the entire Material-UI formControlLabel to select it, not just the text

Creating a file solely for route definitions and then employing them within index.js

Ways to identify individuals who have visited a website on multiple occasions within a 60-day timeframe starting from their initial visit

Using V-For with data fetched from an axios request: A step-by-step guide