Update multiple rows with one query

Question

How can I update hundreds of rows at once?

Like: UPDATE table SET a = ? WHERE b = ? AND c = 1

but for many rows. The ? parameters are arrays...

I read this answer but it uses CASE and I don't think I can do that...

Right now I have something like this:

foreach($values as $key => $value)
  $res = $pdo->prepare('UPDATE table SET a = ? WHERE b = ? AND c = 1');
  $res->execute(array($value, $key));
}

Does every element defined in `b` have an associated element in `a`? In other words, are you try to set the `a` column of each record to a different value? — Dan J, Feb 22 '12 at 21:51
djacobson, exactly (not the same value, each record will have a different value, associated with the value of b). I'm doing this in a foreach loop right now, but I was thinking there's a shorter way to do thing with just mysql — Alex, Feb 22 '12 at 21:53
try this.http://stackoverflow.com/questions/8626510/create-dynamic-number-of-variables — user1015314, Feb 22 '12 at 22:05

score 8 · Accepted Answer · edited May 23 '17 at 12:16

8

To do it in a single run of a query, you'd need to use a CASE and assemble the parameters programmatically. SQL doesn't support variadic prepared statements, and only simple values can be parameterized.

Alternatively, define a statement to only take data for one row at a time and run the query in a loop. Repeated execution is how prepared statements are designed to be used for cases like this.

try {
    $query = $db->prepare('UPDATE table SET a = ? WHERE b = ? AND c = 1');
    foreach ($as as $i => $a) {
        $query->execute(array($a, $bs[$i]));
    }
} catch (PDOException $e) {
    ...
}

edited May 23 '17 at 12:16

Community

1
1

answered Feb 22 '12 at 22:00

outis

75,655
22
151
221

can you create a case statement and still take advantage of prepared statements for SQL Injection or would you have to escape each input "as the old days" – JM4 Jul 16 '13 at 20:15
@JM4: it depends on what you need to parameterize. Prepared statements are analogous to functions, but only values (and not, e.g., column names) can be parameterized. As long as that's all you need, then a prepared statement will work fine. However, I recommend a solution analogous to my sample code, as it's simpler overall. Unlike functions, arguments to prepared statements can't be re-used, generally speaking, so instead you'll have to have separate parameters and repeat the values, which is error-prone. – outis Aug 09 '13 at 20:23

score 4 · Answer 2 · answered Feb 22 '12 at 22:00

4

Use the CASE method as described in the link you provided, but build the query dynamically with the values you want.

Likely, this will be built with a for loop similar to how you're already doing it, but you will end up with a single query rather than querying your database every iteration.

answered Feb 22 '12 at 22:00

MysticXG

1,437
10
10

the only downside to this (and it is a recommendation I myself make) is that you are unable to escape the inputted data with prepared statements like you otherwise would in single row statements. – JM4 Jul 16 '13 at 17:34

score 2 · Answer 3 · answered Feb 22 '12 at 22:38

2

Another way would be to insert your key value pairs (all at once) into a temporary table then do something like this:

UPDATE table t
SET t.a = (SELECT p.a FROM tmp p WHERE p.b = t.b)
WHERE t.b IN (SELECT p.b FROM tmp p) AND t.c = 1

answered Feb 22 '12 at 22:38

Marcus Adams

53,009
9
91
143

Update multiple rows with one query

3 Answers3

Linked

Related