user3262424
user3262424

Reputation: 7479

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 = [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 )?

Upvotes: 2

Views: 9616

Answers (3)

JamesT
JamesT

Reputation: 3028

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

Upvotes: 1

Skrol29
Skrol29

Reputation: 5552

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;

Upvotes: 2

Patrick
Patrick

Reputation: 328

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

Upvotes: 1

Related Questions