MySQL -- Multiple Updates In One Statement?

I have a table in MySQL that looks like this:

studentID subjectID anotherID anotherID2, AnotherID3, studentScore

This table has about 100M records of students in it.

---

Suppose that I have the following information stored in two python lists:

listStudentIDs = [开发者_JS百科1, 123, 33, 5524, 425653]

listNewScores = [12.76, 73.2, 83.893, 92.3, 53.6]

Is it possible to have one query that will update all studentScore fields for all students (whose IDs are in listStudentIDs) if, and only if, their score in listNewScores is greater than their score currently stored in the database ( studentScore )?

---

In your example, they are 5 student ids for only 4 scores to update.

If your list of students to update is not to long, then you can have an update in one query like this :

UPDATE t_student
SET studentScore = CASE
 WHEN studentID=1    THEN 12.76
 WHEN studentID=123  THEN 73.2
 WHEN studentID=33   THEN 83.893
 WHEN studentID=5524 THEN 92.3
 ELSE studentScore END
WHERE studentID IN (1, 123, 33, 5524, 425653)

Nevertheless less you may consider to use a SQL statement including several queries :

UPDATE t_student SET studentScore = 12.76  WHERE studentID = 1;
UPDATE t_student SET studentScore = 73.2   WHERE studentID = 123;
UPDATE t_student SET studentScore = 83.893 WHERE studentID = 33;
UPDATE t_student SET studentScore = 92.3   WHERE studentID = 5524;

---

Unfortunately, no, MySql can't handle this. Otherwise, I'd be using it all the time!

---

Try putting your two lists into a temporary table (studentID, studentScore).

Then you should be able to run a single query to update the main table accordingly.

UPDATE studentTable t1 
JOIN tempTable t2 ON t1.studentID = t2.studentID 
SET t1.studentScore = t2.studentScore WHERE t2.studentScore > t1.studentScore