The mysql UPDATE statement is very simple. It updates a field of a piece of data. It is generally written as follows:

UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';

If you update the same field to the same value, mysql is also very simple. You can modify the following where:

UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');

Notice here, other_values is a comma separated string, such as 1,2,3

1 conventional scheme
If you update multiple pieces of data to different values, many people may write:

foreach ($display_order as $id => $ordinal) {
    $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
    mysql_query($sql);
}

That is, update records one by one.

One record is update d once, which has poor performance and is easy to cause blocking.

2 efficient scheme
So can you implement batch update with an sql statement?

2.1 CASE WHEN
mysql does not provide a direct way to implement batch updates, but it can be implemented with a few tips.

UPDATE mytable SET
    myfield = CASE id
        WHEN 1 THEN 'value'
        WHEN 2 THEN 'value'
        WHEN 3 THEN 'value'
    END
WHERE id IN (1,2,3)

Here we use the small skill of case when to realize batch update.

for instance:

UPDATE categories SET
    display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END
WHERE id IN (1,2,3)

This sentence means to update the display_order field:

Display if id=1_ The value of order is 3,
Display if id=2_ The value of order is 4,
Display if id=3_ The value of order is 5.
That is, the conditional statements are written together.

The where part here does not affect the code execution, but will improve the efficiency of sql execution.

Ensure that the sql statement executes only the number of rows that need to be modified. Here, only 3 rows of data are updated, while the where clause ensures that only 3 rows of data are executed.

3.2 updating multiple values
If you update multiple values, you only need to modify them slightly:

UPDATE categories SET
    display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

Here, you have completed a mysql statement to update multiple records.

However, to be used in business, it needs to be combined with the server language.

3.3 encapsulation into PHP functions
In PHP, we encapsulate this function into a function and call it directly later.

To improve availability, we consider dealing with a more comprehensive situation.

The following data needs to be updated according to id and parent_ The id field updates the contents of the post table.

Where, the value of id will change to parent_ The value of id is the same.

 
$data = [
    ['id' => 1, 'parent_id' => 100, 'title' => 'A', 'sort' => 1],
    ['id' => 2, 'parent_id' => 100, 'title' => 'A', 'sort' => 3],
    ['id' => 3, 'parent_id' => 100, 'title' => 'A', 'sort' => 5],
    ['id' => 4, 'parent_id' => 100, 'title' => 'B', 'sort' => 7],
    ['id' => 5, 'parent_id' => 101, 'title' => 'A', 'sort' => 9],
];

For example, we want parent_ Records with id 100 and title A are updated in batches according to different IDS:

echo batchUpdate($data, 'id', ['parent_id' => 100, 'title' => 'A']);
The PHP code implemented by batchUpdate() is as follows:

/**
 * Batch update function
 * @param $data array Data to be updated, 2D array format
 * @param array $params array The one-dimensional array corresponding to the key value under the condition of the same value
 * @param string $field string For conditions with different values, the default value is id
 * @return bool|string
 */
function batchUpdate($data, $field, $params = [])
{
   if (!is_array($data) || !$field || !is_array($params)) {
      return false;
   }

    $updates = parseUpdate($data, $field);
    $where = parseParams($params);

    // Get the values of all columns whose key name is $field, put single quotes around the values, and save them in the $fields array
    // array_ PHP5 is required for the column() function 5.0 +. If it is smaller than this version, you can implement it yourself,
    // Reference address: http://php.net/manual/zh/function.array-column.php#118831
    $fields = array_column($data, $field);
    $fields = implode(',', array_map(function($value) {
        return "'".$value."'";
    }, $fields));

    $sql = sprintf("UPDATE `%s` SET %s WHERE `%s` IN (%s) %s", 'post', $updates, $field, $fields, $where);

   return $sql;
}

/**
 * Batch update condition for converting two-dimensional array to CASE WHEN THEN
 * @param $data array Two dimensional array
 * @param $field string Listing
 * @return string sql sentence
 */
function parseUpdate($data, $field)
{
    $sql = '';
    $keys = array_keys(current($data));
    foreach ($keys as $column) {

        $sql .= sprintf("`%s` = CASE `%s` \n", $column, $field);
        foreach ($data as $line) {
            $sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], $line[$column]);
        }
        $sql .= "END,";
    }

    return rtrim($sql, ',');
}

/**
 * Parse where condition
 * @param $params
 * @return array|string
 */
function parseParams($params)
{
   $where = [];
   foreach ($params as $key => $value) {
      $where[] = sprintf("`%s` = '%s'", $key, $value);
   }
   
   return $where ? ' AND ' . implode(' AND ', $where) : '';
}

Get such a batch update SQL statement:

UPDATE `post` SET `id` = CASE `id` 
WHEN '1' THEN '1' 
WHEN '2' THEN '2' 
WHEN '3' THEN '3' 
WHEN '4' THEN '4' 
WHEN '5' THEN '5' 
END,`parent_id` = CASE `id` 
WHEN '1' THEN '100' 
WHEN '2' THEN '100' 
WHEN '3' THEN '100' 
WHEN '4' THEN '100' 
WHEN '5' THEN '101' 
END,`title` = CASE `id` 
WHEN '1' THEN 'A' 
WHEN '2' THEN 'A' 
WHEN '3' THEN 'A' 
WHEN '4' THEN 'B' 
WHEN '5' THEN 'A' 
END,`sort` = CASE `id` 
WHEN '1' THEN '1' 
WHEN '2' THEN '3' 
WHEN '3' THEN '5' 
WHEN '4' THEN '7' 
WHEN '5' THEN '9' 
END WHERE `id` IN ('1','2','3','4','5')  AND `parent_id` = '100' AND `title` = 'A'

The generated SQL lists all the situations.

However, only the records with IDS 1, 2 and 3 are updated because there are WHERE restrictions.

If only one column needs to be updated, and other conditions are not limited, the incoming $data can be simpler:

$data = [
    ['id' => 1, 'sort' => 1],
    ['id' => 2, 'sort' => 3],
    ['id' => 3, 'sort' => 5],
];
echo batchUpdate($data, 'id');

When such a data format is passed in, you can modify the records with IDs from 1 to 3 and change sort to 1, 3 and 5 respectively.

Get SQL statement:

UPDATE `post` SET `id` = CASE `id` 
WHEN '1' THEN '1' 
WHEN '2' THEN '2' 
WHEN '3' THEN '3' 
END,`sort` = CASE `id` 
WHEN '1' THEN '1' 
WHEN '2' THEN '3' 
WHEN '3' THEN '5' 
END WHERE `id` IN ('1','2','3')