Mysql Insert if not exist in two column

Question

I looked into MySQL duplicate key but cant figure it out.

I have a table like below:

id   series   chapter   path(can be unique)

I want only insert data and not update. Lets say I have data like below:

seri:Naruto, klasor:567 ==> If both of these exist in table then do not insert.

seri:Naruto, klasor:568 ==> If Naruto exist but 568 does not exist then do insert.

How can I achieve this?

Answer 1

Easiest way would be to define unique index with two columns on that table:

ALTER TABLE yourtable ADD UNIQUE INDEX (seri,klasor);

You may also define two column primary key, which would work just as well.

Then use INSERT IGNORE to only add rows when they will not be duplicates:

INSERT IGNORE INTO yourtable (seri, klasor) VALUES ('Naruto',567);
INSERT IGNORE INTO yourtable (seri, klasor) VALUES ('Naruto',568);

Edit: As per comments, you can't use UNIQUE INDEX which complicates things.

SET @seri='Naruto';
SET @klasor=567;
INSERT INTO yourtable 
 SELECT seri,klasor FROM (SELECT @seri AS seri, @klasor AS klasor)
  WHERE NOT EXISTS (SELECT seri, klasor FROM yourtable WHERE seri=@seri AND klasor=@klasor);

You may use the above query with two local variables or convert it to single statement by replacing the local variables with actual values.

Better way would be to use stored procedure:

CREATE PROCEDURE yourinsert (vseri VARCHAR(8), vklasor INT)
BEGIN
 DECLARE i INT;
 SELECT COUNT(*) INTO i FROM yourtable WHERE seri=vseri AND klasor=vklasor;

 IF i=0 THEN
  INSERT INTO yourtable (seri,klasor) VALUES (vseri, vklasor);
 END IF;
END;

This would allow you to perform the INSERT using:

CALL yourinsert('Naruto',567);