CODEWITHSUNDEEP
javascriptmysqlsqlnode.jsnode-mysql
Angular noob
Angular noob

Reputation: 437

How to insert array elements into db only if they do not already exist

Here is my db schema:

CREATE TABLE technologies (
    technologyName VARCHAR(50) NOT NULL PRIMARY KEY
);

CREATE TABLE videos (
    videoId             INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title               VARCHAR(300) NOT NULL,
    url                 VARCHAR(300) NOT NULL,
    description         VARCHAR(300) NOT NULL,
    channelName         VARCHAR(300) NOT NULL
);

CREATE TABLE technology_video_map (
    videoId        INT,
    technologyName VARCHAR(50),

    PRIMARY KEY (videoId, technologyName),
    FOREIGN KEY (videoId) REFERENCES videos(videoId),
    FOREIGN KEY (technologyName) REFERENCES technologies(technologyName)
);

I want the user to submit a video:

var input = {
  title: 'Grunt makes your web development better!',
  url: 'https://www.youtube.com/watch?v=TMKj0BxzVgw',
  description: 'If you\'re not using a task runner/build system like Grunt or Gulp...',
  channelName: 'LearnCode.academy',
  technologies: ['Grunt', 'JavaScript']
};

And I want to insert it into the db. Inserting only the video is easy enough for me:

var technologies = input.technologies; // save for later
delete input.technologies;
connection.query('INSERT INTO videos SET ?', input, function (err, result) {
    var videoId = result.insertId;
});

(connection comes from the brilliant node-mysql)

What I am having trouble with, is inserting the technologies into the db. I have identified that the first step is:

  1. Insert elements in input.technologies into the technologies table if they do not already exist.

I can only imagine doing this using a for loop or something which is yucky.

The second step, I think, is:

  1. Insert a new record into the junction table, technology_video_map from the connection.query callback, as we need the videoId.

Again, I cannot imagine an idiomatic way to do this using SQL. Can someone please guide me?

Upvotes: 2

Views: 292

Answers (2)

Rohit Gupta
Rohit Gupta

Reputation: 4191

You have to check for its existence

if not exists(select technologyName from technoligies 
              where technologyName='$technologies')
insert (..)

If you are not using a stored procedure, then fire a query to fetch it. If it fails, then insert it.

Upvotes: 0

Walker Boh
Walker Boh

Reputation: 770

You can use INSERT IGNORE and join your input.technologies array. This will insert records that do not exist and will ignore records that already exist

var query = "INSERT IGNORE INTO technologies (technologyName) VALUES ('" + input.technologies.join("'),('") + "')";

Upvotes: 3

Related Questions