Very High Speed Batch Update Multiple Rows of a Table in Single Query

Oct 13, 2011 · 1 minute read
Category: mysql

This is post is now quite old and the the information it contains may be out of date or innacurate.

If you find any errors or have any suggestions to update the information please let us know or create a pull request on GitHub

If you need to update a large number of rows on a single table then your first reaction may be to write a loop that updates one row at a time.

Of course if the table is large then this can result in a very large number of SQL queries.

Taking a bit of inspiration from this post I have put together this simple PHP function that will allow you to update as many rows as you want.

The trade off is basically memory usage as you build up a big array of row update information preparing for the batch. For this reason you may want to tune the number of rows you update per query.

function bulkUpdateSingleColumn($table, $id_column, $update_column, array &$idstovals){
        $sql = "update $table set $update_column = CASE $id_column ";
        foreach($idstovals as $id=>$val){
            $sql .= " WHEN '$id' THEN '$val' \n";
        }
        $sql .= " END 
        WHERE $id_column in (" . implode(',', array_keys($idstovals)) . ")";
//debugging info
        echo '<small>'.$sql.'</small>';
        $idstovals=array();        
        db_query($sql);       
        done();        
    }