CODEWITHSUNDEEP
pythonmysqlmysql-python
Arun Kalyanaraman
Arun Kalyanaraman

Reputation: 648

On Duplicate Key 2 Unique columns

I'm trying to update the values in a row if two columns in the same row have already been entered. In the code below, student_id and practice_test_id have to be unique in order for the rest of the data to update. For example, if value for (student_id, practice_test_id) = (1,1) and when I try to insert new data into my table and (student_id, practice_test_id) for the new row is also (1,1) it just updates the previous row. However, if (student_id, practice_test_id) is (1,2) or (2,1) it will not update and it will make a new row, essentially updating only when both fields at the same time have already been inputted into the table. Here is my current code to insert data, but I was wondering how I would use ON DUPLICATE KEY UPDATE to update data when the two fields in the entry match. Thanks so much for your help in advance!!

INSERT INTO test_results 
(student_id, practice_test_id, section_1_score, 
 section_1_missed, section_2_score, section_2_missed) 
VALUES (%s, %s, %s, %s, %s, %s)

Upvotes: 0

Views: 34

Answers (1)

Fabricator
Fabricator

Reputation: 12782

First (student_id, practice_test_id) needs to be the primary key, or unique key. Then you can do the following:

insert into test_results
(student_id, practice_test_id, section_1_score, 
 section_1_missed, section_2_score, section_2_missed) 
VALUES (%s, %s, %s, %s, %s, %s)
on duplicate key update section_1_score=values(section_1_score),
  section_1_missed=values(section_1_missed), 
  section_2_score=values(section_2_score),
  section_2_missed=values(section_2_missed)

Upvotes: 1

Related Questions