MySQL update multiple user rows in same query using assoc array

Question

I have a users array that looks like: array(1 => 10, 2 => 11 ...and so on for 100k+ values);

I am currently looping through each user to update the db row. It's very slow, ~ 30 seconds.

foreach ($users as $user_id => $plan_length) {
  mysql_query("UPDATE users SET plan_length = $plan_length WHERE id = $user_id");
}

Is there a way to combine this into 1 query?

Ps.: I am aware of PDO guys, but I am focusing on the query itself here. No PDO for this client's project.

Ps. 2: If a solution is possible with PDO, we'll use PDO.

Ps. 3: The value of plan_length is number of months a user has been on a plan, which currently the maximum value is 20.

***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Aug 31 '17 at 14:20
https://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query — Frankich, Aug 31 '17 at 14:20
Updates can take a while, especially if the table is not indexed. Do you have keys on this table? What are they? — Jay Blanchard, Aug 31 '17 at 14:21
If you were using PDO you could use a prepared statement, which could do exactly what you want. But, is there a logic to the `plan_length`? — KIKO Software, Aug 31 '17 at 14:37
Since you are using the index in the query it is going as fast as it can. The problem is that you have unique values for plan length for each user. You could find all users with the same plan length an do a sinrle query for that group but you might not gain much. — Jay Blanchard, Aug 31 '17 at 14:39
@KIKOSoftware We've got another `sales` table which logs all monthly subscription payments. This query here is taking data from that table to update the users with their plan length (how many rows a user has in `sales` basically). — Dan P., Aug 31 '17 at 14:39
We'll be updating the users' columns as the sale is made from now on, but until that's done, we'll be using this query here. — Dan P., Aug 31 '17 at 14:42
@Dan P. That sound like you would only need a single query that joins the `users` and `sales` tables to update the `plan_length` column. That could run pretty fast. Googling gave me this: http://www.mysqltutorial.org/mysql-update-join — KIKO Software, Aug 31 '17 at 14:46
Trying. https://stackoverflow.com/questions/45985067/mysql-cross-table-update — Dan P., Aug 31 '17 at 15:26

score 1 · Answer 1 · answered Aug 31 '17 at 14:43

1

You can write a update query like this.

UPDATE 
 users 
SET
 plan_length = 
   CASE user_id
      WHEN 1 THEN 10 
      WHEN 2 THEN 11
      [...] 
      ELSE plan_length
   END
WHERE 
 id IN(1, 2, [....])

answered Aug 31 '17 at 14:43

Raymond Nijland

11,488
2
22
34

Neodan · Accepted Answer · 2017-08-31T14:47:30.883

0

It's imposible, you can group only by $plan_length, then you can use query like this:

/*
  $users example:
  $users = [
      '1' => [12, 132, 456, 487],
      '3' => [14, 122, 356, 547],
  ];
 */
foreach ($users as $plan_length => $user_ids) {
  mysql_query("UPDATE users SET plan_length = $plan_length WHERE id IN ('" . implode("', '", $user_ids) . "')");
}

--- UPDATE ---

If the $plan length value is different for every user, then it means that you must do a query per user. You can execute multiple queries per request.

$sqls = [];
$i = 0;
foreach ($users as $user_id => $plan_length) {
    $sqls[] = "UPDATE users SET plan_length = $plan_length WHERE id = $user_id";
    if (++$i => 500) {
        mysql_query(implode('; ', $sqls));
        $sqls = [];
        $i = 0;
    }
}
if (!empty($sqls))
    mysql_query(implode('; ', $sqls));

edited Aug 31 '17 at 14:47

answered Aug 31 '17 at 14:38

Neodan

5,154
2
27
38

@RaymondNijland No, if the `$plan_length` value is the same for all users in query (-; – Neodan Aug 31 '17 at 14:40
it isn't the same – Raymond Nijland Aug 31 '17 at 14:41
That was my idea as well, but there might be very many users... in one plan. It's probably better to restrict it to, say, a 1000 users per query? I have no idea how much MySQL could cope with. – KIKO Software Aug 31 '17 at 14:41
Values for `plan_length` are not all the same, but they're all between 1 and 20. – Dan P. Aug 31 '17 at 14:45
@DanP. I updated my answer. If `plan_length` is between 1 ant 20 then it means that you will have max 20 groups of users, it means max 20 queries :) Try my first solution. – Neodan Aug 31 '17 at 14:48
With the info I've given this seems like the best solution. Thanks! – Dan P. Aug 31 '17 at 15:12
If you know how to help there :) https://stackoverflow.com/questions/45985067/mysql-cross-table-update – Dan P. Aug 31 '17 at 15:26

MySQL update multiple user rows in same query using assoc array

2 Answers2