Duplicate key update MySQL not updating

2018-06-23 19:41:26

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?

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.

链接地址: http://www.djcxy.com/p/66762.html

上一篇: 如何使表面视图透明

下一篇: 重复密钥更新MySQL不更新