[Solved] Updating multiple rows with different values
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Updating multiple rows with different values

Database type:

I got this table in my MySQL database, 'users'. It has the fields 'id' and 'value'.

Now, I want to update lots of rows in this table with a single SQL query, but many rows should get a different value. Currently, I'm using this:

UPDATE users
    SET value = CASE id
        WHEN 1 THEN 53
        WHEN 2 THEN 65
        WHEN 3 THEN 47
        WHEN 4 THEN 53
        WHEN 5 THEN 47
    END
WHERE id IN (1,2,3,4,5)

This works. But I feel I could do some optimization since there are only about 3 or 4 different values I'm assigning to the rows. As you can see, right now these are 47, 53 and 65. Is there a way I can update all rows that get the same value simultaneously within the same query? Or, is there another way I can optimize this?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `users` ADD INDEX `users_idx_id` (`id`);
The optimized query:
SELECT
        users.value 
    FROM
        users 
    WHERE
        users.id IN (
            1, 2, 3, 4, 5
        )

Related Articles



* original question posted on StackOverflow here.