update a table with an array just with one query

Question

I have a table like this:

I receive an array like this:

 $data = array(
   array('m_id'=>1,'d_id'=>101,'available'=>0),
   array('m_id'=>1,'d_id'=>102,'available'=>1),
   array('m_id'=>1,'d_id'=>103,'available'=>1),
   array('m_id'=>1,'d_id'=>104,'available'=>0),
   array('m_id'=>1,'d_id'=>105,'available'=>0)
 );

My question is how I can update the table just with one query like this:

  $query = "UPDATE tbl_name SET available='".$data[0]['available']."'" WHERE conditon1";

I mean update table once instead of 5 query.

Tim Biegeleisen · Answer 1 · 2017-07-09T11:45:20.163

2

In pure MySQL, you could use a CASE expression in the UPDATE to handle this:

UPDATE tbl_name
SET available = CASE WHEN d_id = 101 THEN 0
                     WHEN d_id = 102 THEN 1
                     WHEN d_id = 103 THEN 1
                     WHEN d_id = 104 THEN 0
                     WHEN d_id = 105 THEN 0 END
WHERE d_id IN (101, 102, 103, 104, 105)

If, on the other hand, you wanted to iterate over your array of arrays and then issue UPDATE statements, you could try the following:

foreach ($data as $entry) {
    $query = "UPDATE tbl_name SET available=".$entry['available']." WHERE d_id=".$entry['d_id'];
    // execute $query ...
}

edited Jul 09 '17 at 11:45

answered Jul 09 '17 at 11:27

Tim Biegeleisen

502,043
27
286
360

Biegelesen. the array I received , is not static , it's possible to receive array with to records contains `m_id`, `d_id`, `available`. how can I handle it with foreach or other method – korush Jul 09 '17 at 11:37
@korush I updated my answer to attempt to show how to use PHP's `foreach` loop to iterate over your array. – Tim Biegeleisen Jul 09 '17 at 11:49
1

the problem is the query executed as the length of the array. I want just onec. – korush Jul 09 '17 at 12:00
Then you would need to build a dynamic query. This could be ugly. – Tim Biegeleisen Jul 09 '17 at 12:07

score 1 · Answer 2 · answered Jul 09 '17 at 11:27

1

Possible duplicate of this one. In a nutshell - you need to use either CASE conditional or INSERT ... ON DUPLICATE KEY UPDATE construct.

answered Jul 09 '17 at 11:27

bbb

702
3
6

Nisse Engström · Answer 3 · 2017-08-07T00:15:37.287

Based on the data you have provided, where all rows reference the same columns, and always update the same column, you can create a simple CASE expression:

/* Mostly untested */

function update_array ($data)
{
  if (empty ($data))
    return NULL;

  if (count ($data) === 1) {

    /* Simple query for single update */

    $mi = $data[0]['m_id'];
    $di = $data[0]['d_id'];
    $av = $data[0]['available'];

    $sql = <<<_
UPDATE `tbl_name`
   SET `available` = $av
 WHERE `m_id`= $mi AND `d_id`= $di

_;

  } else {

    /* CASE WHEN query for multiple updates */

    $sql = "UPDATE `tbl_name`\n   SET `available` = \n           CASE ";

    foreach ($data as $d) {
      $mi = $d['m_id'];
      $di = $d['d_id'];
      $av = $d['available'];
      $sql .= "WHEN `m_id`=$mi AND `d_id`=$di THEN $av\n                ";
    }

    /* Never forget the ELSE clause! */

    $sql .= "ELSE `available`\n           END\n";
  }

  return $sql;
}

This test program:

$data = array(
  array('m_id'=>1, 'd_id'=>101, 'available'=>0),
  array('m_id'=>1, 'd_id'=>102, 'available'=>1),
  array('m_id'=>1, 'd_id'=>103, 'available'=>1),
  array('m_id'=>1, 'd_id'=>104, 'available'=>0),
  array('m_id'=>1, 'd_id'=>105, 'available'=>0)
);

echo update_array ($data);

outputs:

UPDATE `tbl_name`
   SET `available` =
           CASE WHEN `m_id`=1 AND `d_id`=101 THEN 0
                WHEN `m_id`=1 AND `d_id`=102 THEN 1
                WHEN `m_id`=1 AND `d_id`=103 THEN 1
                WHEN `m_id`=1 AND `d_id`=104 THEN 0
                WHEN `m_id`=1 AND `d_id`=105 THEN 0
                ELSE `available`
           END

Note:

Never forget the ELSE clause in a CASE WHEN update. If you do, then all the non-matching rows will be overwritten with a blank value.
This code blindly assumes that the data contains the expected indexes, and that the data is of the appropriate type.
Some say that you should include a WHERE clause to limit the number of rows to be updated for performance reasons. I haven't seen any data to back this up, but if it is a concern, then a simple improvement might be to narrow it down using the d_id column. Just add the follow at the end of the CASE WHEN query:
```
$ids = array_column ($data, 'd_id');
$sql .= ' WHERE `d_id` IN (' . implode (', ', $ids) . ")\n";
```
You still need the ELSE in the CASE expression because this WHERE clause is not guaranteed to exclude all the unwanted rows. Writing a complete WHERE clause will probably be much more complicated, depending on the exact nature of your data.

update a table with an array just with one query

3 Answers3