I’m trying to optimize my application's database performance. Currently, I’m running individual UPDATE queries inside a loop to update different price values for various product IDs. Is there a more efficient way to execute this as a single SQL statement to reduce the number of database round-trips? I need each specific ID to receive its own unique value without affecting other rows in the table.
3 answers
The most efficient standard way to do this in MySQL is by using the UPDATE ... SET ... CASE syntax. Instead of multiple queries, you construct a single query that defines logic for each ID. For example: UPDATE products SET price = CASE WHEN id = 1 THEN 10 WHEN id = 2 THEN 20 ELSE price END WHERE id IN (1, 2). This approach is significantly faster for batch sizes of 100-500 rows because it reduces the overhead of transaction logging and network latency. By including the WHERE clause with the specific IDs, you ensure that MySQL doesn't perform a full table scan, keeping the operation highly performant even on larger datasets common in enterprise software.
Have you considered using INSERT INTO ... ON DUPLICATE KEY UPDATE as an alternative for bulk operations, especially if you have a large volume of data coming from a temporary table?
You can also use a JOIN with a temporary values list if your MySQL version supports it. This keeps the query cleaner than a giant CASE block.
I agree with Melissa; joining against a derived table or a JSON object (if you're on MySQL 8.0+) is much more readable and maintainable when your update logic starts getting complicated!
Brandon makes an excellent point for high-concurrency environments. The ON DUPLICATE KEY UPDATE trick is often faster than a long CASE statement when you have thousands of rows to update, as it leverages the primary key more effectively. You essentially "insert" the new values, and if the ID already exists, MySQL just updates the row with the new data. In my experience with large-scale Software Development projects, this method scales much better and results in fewer deadlocks compared to complex conditional logic within a standard update statement.