Ignore insert if exist in table in mysql?

Question

I have 6 columns in my table:

Id  | Name | Mail id | Gender | Contact Number | father name

while inserting a data into table i wanted to check condition like if Name,mailid,contact number already exists then insert should not happen else record should be inserted.

Can any one suggest how to check the condition while inserting a record.

Maybe this help you: http://stackoverflow.com/questions/913841/mysql-conditional-insert — kitek, Nov 17 '13 at 17:24
If the tuple `Name, MailID, ContactNumber` should be unique, you should define a unique index on it to enforce that constraint. Then you can use `INSERT IGNORE` which is simpler. Otherwise go with Muhammed Ali's answer. — Hut8, Nov 17 '13 at 17:28
refer the link below it works pretty well http://stackoverflow.com/a/3164741/3209804 — Varshaan, Nov 24 '15 at 08:22

score 1 · Answer 1 · answered Nov 17 '13 at 17:23

1

IF NOT EXISTS (SELECT * FROM Table_Name WHERE Condition you are checking)
BEGIN
  INSERT INTO .............   ---<----- Your Insert Statement.....

END

answered Nov 17 '13 at 17:23

M.Ali

62,698
12
85
116

It should be `IF NOT EXISTS ...` – Lucia Pasarin Nov 17 '13 at 17:24
Cheers this what happens when you'er watching tele and answering a question :) – M.Ali Nov 17 '13 at 17:25

score 0 · Answer 2 · answered Nov 17 '13 at 17:27

0

You can define an index on multiple columns, e.g.:

CREATE UNIQUE INDEX arbitrary_index_name ON table_name (Name, mailid, contactnumber);

answered Nov 17 '13 at 17:27

Milina Udara

587
1
9
24

score 0 · Answer 3 · answered Mar 18 '15 at 08:30

I also faced similar situation, you can do this by adding unique constraint to your table and using 'insert ignore' statement to add data.

Create table statement:

CREATE TABLE Student (
Id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50),
Mailid VARCHAR(50),
Gender CHAR,
contactnumber BIGINT,
fathername VARCHAR(50),
UNIQUE(NAME,Mailid,contactnumber));

Insert Ignore statement:

INSERT IGNORE INTO student(NAME, Mailid,Gender,contactnumber,fathername) VALUES('Shekhar', 's@s.com', 'M', 987654321, 'Joshi');

Ignore insert if exist in table in mysql?

3 Answers3