CODEWITHSUNDEEP
mysqljsp
Ghost
Ghost

Reputation: 149

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

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.

Upvotes: 1

Views: 179

Answers (4)

Jeff
Jeff

Reputation: 918

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

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

Upvotes: 1

Filipe Silva
Filipe Silva

Reputation: 21677

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

Upvotes: 1

Alex Wittig
Alex Wittig

Reputation: 2900

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+"')");

Upvotes: 1

Cillier
Cillier

Reputation: 1021

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

Upvotes: 0

Related Questions