CODEWITHSUNDEEP
mysqlsqldatabaseoracle-database
Free Lancer
Free Lancer

Reputation: 1000

More efficient way to write multiple UPDATE queries

Is there a better / more efficient / shorter way to write this SQL Query:

UPDATE mTable SET score = 0.2537 WHERE user = 'Xthane' AND groupId = 37;
UPDATE mTable SET score = 0.2349 WHERE user = 'Mike' AND groupId = 37;
UPDATE mTable SET score = 0.2761 WHERE user = 'Jack' AND groupId = 37;
UPDATE mTable SET score = 0.2655 WHERE user = 'Isotope' AND groupId = 37;
UPDATE mTable SET score = 0.3235 WHERE user = 'Caesar' AND groupId = 37;

Upvotes: 1

Views: 2311

Answers (4)

spencer7593
spencer7593

Reputation: 108510

Your original statements look short enough, and are easy enough to understand, and you can determine whether there were any rows affected on each of those separate UPDATE statements.

For a large number of statements, however, there's a considerable amount of overhead making "roundtrips" to the database to execute each individual statement. You can get much faster execution (shorter elapsed time) for a large set of updates by "batching" the updates together in a single statement execution.

So, it depends on what you are trying to achieve.

Better? Depends on how you define that. (Should the statements be more understandable, easier to debug, less resource intensive?

More efficient? In terms of reduced elapsed time, yes, there are other ways to accomplish these same updates, but the statements are not as easy to understand as yours.

Shorter? In terms of SQL statements with fewer characters, yes, there are ways to achieve that. (Some examples are shown in other answers, but note that the effects of the statements in some of those answers is significantly DIFFERENT than your statements.)

The actual performance of those alternatives is really going to depend on the number of rows, and available indexes. (e.g. if you have hundreds of thousands of rows with groupId = 37, but are only updating 5 of those rows).

Upvotes: 0

bobwienholt
bobwienholt

Reputation: 17610

You could create a temporary table, insert score, user and groupid for all the records you want to update then do something like this:

UPDATE
FROM mTable m
INNER JOIN tmpTable t
  ON m.groupId = t.groupId
  AND m.user = t.user
SET m.score = t.score;

Upvotes: 0

Taryn
Taryn

Reputation: 247870

You can use a CASE statement to perform this type of UPDATE.

UPDATE mTable
SET score 
   = CASE user
        WHEN 'Xthane' THEN 0.2537
        WHEN 'Mike' THEN 0.2349
        WHEN 'Jack' THEN 0.2761
        WHEN 'Isotope' THEN 0.2655
        WHEN 'Caesar' THEN 0.3235
        ELSE score
     END
WHERE groupId = 37

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171579

UPDATE mTable 
SET score = 
    case user
        when 'Xthane' then 0.2537
        when 'Mike' then 0.2349
        when 'Jack' then 0.2761
        when 'Isotope' then 0.2655
        when 'Caesar' then 0.3235
        else score
    end
where groupId = 37

Upvotes: 7

Related Questions