How to bulk update mysql data with one query?

How to bulk update mysql data with one query?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Consolidating Multiple UPDATE Statements into a Single Query

Executing separate UPDATE statements for individual rows, as shown below, is functionally correct but inefficient when scaling beyond a few records:

$query = mysql_query("UPDATE a SET fruit = 'apple' WHERE id = '1'");
$query2 = mysql_query("UPDATE a SET fruit = 'orange' WHERE id = '2'");
$query3 = mysql_query("UPDATE a SET fruit = 'peach' WHERE id = '3'");

This approach incurs significant overhead. Each statement requires a distinct round-trip between the application and the database server, independent parsing of the SQL text, and separate execution cycles. If you need to update hundreds or thousands of rows this way, network latency and server resource consumption will skyrocket. The goal is to collapse these individual operations into a single, cohesive query that leverages the database engine's ability to process sets of data in one go.

The most direct solution involves using a CASE statement within the SET clause. This allows you to map specific conditions (like row IDs) to their corresponding new values without needing multiple queries or subqueries. The syntax transforms the logic from "execute this, then execute that" to "for every row, check which ID it has and apply the matching value."

Here is how you would rewrite the example for a MySQL database:

UPDATE a 
SET fruit = CASE id
    WHEN 1 THEN 'apple'
    WHEN 2 THEN 'orange'
    WHEN 3 THEN 'peach'
END;

This single statement instructs the database to iterate through the rows of table a. For each row, it evaluates the id column against the conditions in the CASE block. If the current row's id matches 1, fruit becomes 'apple'; if it matches 2, it becomes 'orange', and so on. Rows not matching any condition retain their existing values, though in this specific example, all three IDs are explicitly covered.

There is a crucial distinction to make here regarding the data types involved in the WHERE clause versus the CASE logic. In your original snippet, you used string literals for both the fruit names and the IDs ('1', '2'). While MySQL often handles implicit type conversion gracefully (treating the integer 1 as equal to the string '1'), it is best practice to match data types explicitly. If your id column is an integer, use integers in the query:

UPDATE a 
SET fruit = CASE id
    WHEN 1 THEN 'apple'
    WHEN 2 THEN 'orange'
    END;

This prevents potential issues with strict SQL modes or unexpected type coercion errors that might arise if the column definition and the query values diverge significantly.

Performance Considerations and Limitations

While consolidating queries improves efficiency, the CASE approach has a practical ceiling. You can only define a finite number of conditions in a single statement. If you are attempting to update data based on hundreds of unique IDs or dynamic sets that change frequently, hard-coding every condition becomes unwieldy and difficult to maintain.

In scenarios where the mapping logic is complex or the list of updates comes from an external source (like a JSON payload or a CSV file), this manual CASE construction isn't feasible at runtime without generating the SQL string dynamically in your application code. In such cases, you might consider using prepared statements with batches if your database driver supports it, or falling back to stored procedures that accept a list of updates as parameters.

It is also worth noting that while mysql_query() (deprecated in favor of PDO or mysqli) was used in the source examples, modern Laravel applications typically rely on Eloquent or raw queries via PDO. If you are working within a Laravel context, constructing this dynamic SQL string requires care to prevent SQL injection. You should never concatenate user input directly into the query string. Instead, use parameter binding if possible, or ensure that any data being mapped is strictly validated and sanitized before it enters the CASE logic.

For very large datasets where even a single massive update could cause locking issues or long transaction times, consider breaking the operation into smaller batches of 500 to 1000 rows. This balances the benefits of reduced round-trips with the need to keep transaction scope manageable.

Our network