0

I have two Unique keys in one table. I'm inserting the data from an csv file. Unique keys are: enrollmentNo and subjectCode

My query is:

Insert into result_stud_det(enrollmentNo,departmentCode,subjectCode,semester,marks,enrSubjCode) values (?,?,?,?,?,?) "
                    + "ON DUPLICATE KEY UPDATE previousMarks=marks, marks=?;

The problem appeared when the data is updating. The "marks" updating in the end is remaining same. The first data on the csv file is copied down to every other column with unique enrollmentNo and to any subjectCodes. It is because it is looking for the unique key "enrollmentNo" only. What do I need to do so that the latest marks, "marks", doesn't have same value after updating it?

Jason
  • 15,017
  • 23
  • 85
  • 116
Manoj Kumar Giri
  • 49
  • 7
  • Is there any chance that your settings your 7th parameter (marks) to the old value instead of the new value unexpectedly? It's hard to tell exactly what's going on without seeing some code snippets. – Vinbot Apr 27 '15 at 17:12

1 Answers1

1

I suspect your problem is related to this question: MySQL behavior of ON DUPLICATE KEY UPDATE for multiple UNIQUE fields

From the accepted answer:

UPDATE in ON DUPLICATE KEY UPDATE is performed if one of the UNIQUE field equals the value to be inserted

It sounds like you are expecting your statement to behave as if you have a single, composite unique key, instead of two separate unique keys.

Community
  • 1
  • 1
Alex Wittig
  • 2,800
  • 1
  • 33
  • 42