How can I ignore the duplicate keys and proceed inserting in mysql?

Question

Im working on reading a .xls file (excel) and inserting it to my database. Im using a jsp to do this and it's working fine if there's no duplicate key.

    patientId = list1.get(i).toString();
    patientFname = list2.get(i).toString();
    patientLname = list3.get(i).toString();
    patientMname = list4.get(i).toString();
    patientGender = list5.get(i).toString();
    patientBirthday = list6.get(i).toString();
    patientAge = list7.get(i).toString();
    patientAddress = list8.get(i).toString();
    patientCompany = list9.get(i).toString();

ResultSet rs=st.executeQuery("SELECT idpatients FROM patients");

    if (rs.first()) {

    }
    st.executeUpdate("insert into patients(idpatients,pfirstname,plastname,pmiddlename,gender,birthdate,age,address,company) values('"+patientId+"','"+patientFname+"','"+patientLname+"','"+patientMname+"','"+patientGender+"','"+patientBirthday+"','"+patientAge+"','"+patientAddress+"','"+patientCompany+"')");

It's not working if there's a duplicate primary key and I would like to ignore that and proceed to the next data.

score 1 · Answer 1 · answered Nov 11 '13 at 15:55

If you really want to just do nothing in the case of a duplicate key, INSERT IGNORE is right up your alley:

st.executeUpdate("insert ignore into patients(idpatients,pfirstname,plastname,pmiddlename,gender,birthdate,age,address,company) values('"+patientId+"','"+patientFname+"','"+patientLname+"','"+patientMname+"','"+patientGender+"','"+patientBirthday+"','"+patientAge+"','"+patientAddress+"','"+patientCompany+"')");

Jeff · Accepted Answer · 2013-11-11T16:13:10.477

1

If you just want to ignore the primary key violation then you can use

INSERT IGNORE INTO patients (columns here) VALUES (values here)

edited Nov 11 '13 at 16:13

answered Nov 11 '13 at 15:55

Jeff

908
2
9
23

score 1 · Answer 3 · edited May 23 '17 at 12:29

1

Use INSERT IGNORE ... or INSERT ... ON DUPLICATE KEY UPDATE. See this answer for a good reference on the differences.

edited May 23 '17 at 12:29

Community

1
1

answered Nov 11 '13 at 15:55

Filipe Silva

21,189
5
53
68

score 0 · Answer 4 · answered Nov 11 '13 at 15:52

Use INSERT ... ON DUPLICATE KEY UPDATE

insert into patients(idpatients,pfirstname,plastname,pmiddlename,gender,birthdate,age,address,company) values('"+patientId+"','"+patientFname+"','"+patientLname+"','"+patientMname+"','"+patientGender+"','"+patientBirthday+"','"+patientAge+"','"+patientAddress+"','"+patientCompany+"' ON DUPLICATE KEY UPDATE idpatients=idpatients;"

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

How can I ignore the duplicate keys and proceed inserting in mysql?

4 Answers4