How do I update multiple data using single query?

Question

This query takes a lot of time to run when the id_array is big. How can I make it faster? Is it maybe possible to do this in one query instead of multiple updates as in my example?

<?php
$sort = 1;
$id_array = array(
    56,
    14,
    99,
    54,
    8,
    92
);
foreach ($id_array as $id) {
    mysqli_query($conn, "UPDATE id_table SET sort = '$sort' WHERE id = '$id'");
    $sort++;
}
?>

Check https://stackoverflow.com/questions/6734231/mysql-update-case-help — Sougata Bose, Aug 06 '19 at 05:59
Sougata, the solution at the link you gave me works perfect. — Peter Karlsson, Aug 06 '19 at 07:13

score 3 · Answer 1 · answered Aug 06 '19 at 06:02

Legend has it that using a prepared statement can be quicker than other types of query when dealing with large datasets - whether this is true or not is for you to decide - but to use a prepared statement you could try like this:

$sql='update id_table set sort = ? where id = ?';
$stmt=$conn->prepare( $sql );
$stmt->bind_param( 'ii', $sort, $id );

foreach( $id_array as $index => $id ){
    $sort=$index+1;
    $stmt->execute();
}

score 1 · Answer 2 · answered Aug 06 '19 at 06:04

1

assuming you have an array as

$id_array = array(56, 14, 99, 54, 8, 92);

and you db driver allow multiple sql statement you could try using sql var a in clause and avoid loop ( avoid also quote for numeric values ) building a proper string for IN clause

$yourArray = implode(', ', $id_array);

mysqli_query($conn, " set  @sort = 0;
 UPDATE id_table SET sort = @sort +1 WHERE id IN  ( ". $your_Array . ")");

answered Aug 06 '19 at 06:04

ScaisEdge

131,976
10
91
107

It feels wrong to use internal variables here, but maybe it's just a feeling – Strawberry Aug 06 '19 at 06:31
I can't get this to work. It feels like a simple solution but sort is not incremented when I run this query. – Peter Karlsson Aug 06 '19 at 07:12

score 0 · Answer 3 · answered Aug 06 '19 at 07:27

This is my final solution. It works great and is much faster than my first attempt.

$sort = 1;
$query = "UPDATE id_table SET sort = CASE ";
foreach($id_array as $id)
    {
    $query = $query . "WHEN id = '$id' THEN '$sort' ";  
    $sort++;
    }
$query = $query . "END";
mysqli_query($conn, $query);

How do I update multiple data using single query?

3 Answers3