Updating multiple rows in MySQL

Question

I'm trying to update multiple rows in one table in MySQL database by doing this. And its not working.

$query = "UPDATE cart SET cart_qty='300' WHERE cart_id = '21';
          UPDATE cart SET cart_qty='200' WHERE cart_id = '23';
          UPDATE cart SET cart_qty='100' WHERE cart_id = '24';";
mysql_query($query,$link);// $link is specified above

Anyone know what is wrong with this.

Looks like three separate (yet structural identical) queries to me. Why do you want to send them as one statement instead of using e.g. a prepared statement three times? — VolkerK, Jul 07 '10 at 22:22

score 5 · Accepted Answer · answered Jul 07 '10 at 21:52

5

From the PHP documentation:

mysql_query() sends a unique query (multiple queries are not supported)

The ; separates SQL statements, so you need to separate the queries if you want to continue using the mysql_query function...

answered Jul 07 '10 at 21:52

OMG Ponies

325,700
82
523
502

well i know you can run multiple queries of INSERT – Ross Jul 07 '10 at 21:55
1

Mutliple queries of insert tend to be concatenated series of value sets, combined by a comma: INSERT INTO cart (id,cart_qty) VALUES (21,300),(23,200),(24,100); – Brendan Bullen Jul 07 '10 at 22:00
if i paste that query in phpMyadmin in SQL tab it work. Any ideas how phpMyadmin makes it work? – Ross Jul 07 '10 at 22:07
@Ross: Because phpMyAdmin allows multiple queries. PHP doesn't, because of SQL injection attacks: http://xkcd.com/327/ – OMG Ponies Jul 07 '10 at 22:09

Brendan Bullen · Answer 2 · 2010-07-07T21:58:56.187

mysql_query can't use multiple queries.

The easiest thing is to just run them separately. I believe you can do multi query but I haven't tried it.

$updateArray = array(21=>300,23=>200,24=>100);
foreach($updateArray as $id=>$value)
{
    $query = "UPDATE cart SET cart_qty='$value' WHERE cart_id = '$id'";
    mysql_query($query,$link);// $link is specified above
}

This will accept a combination of IDs and their corresponding cart value. Looping though, it builds the query and executes it. The array can then come from a variety of sources (results from another query, form inputs or, as in this case, hard-coded values)

Update:

If you really need to execute all in one, heres the PHP info on multi query:

mysqli::multi_query

score 2 · Answer 3 · answered Nov 01 '12 at 10:35

You can do it this way:

UPDATE table
  SET col1 = CASE id
    WHEN id1 THEN id1_v1,
    WHEN id2 THEN id2_v1
  END
  col2 = CASE id
    WHEN id1 THEN id1_v2,
    WHEN id2 THEN id2_v2
  END
  WHERE id IN (id1, id2)

This example shows updating two different columns in two different rows so you can expand this to more rows and columns by cludging together a query like this. There might be some scaling issues that makes the case statement unsuitable for a very large number of rows.

score 0 · Answer 4 · edited May 23 '17 at 12:07

0

You'll need to send them as separate queries. Why not add the queries as strings to an array, then iterate through that array sending each query separtely?

Also check this thread for another idea

edited May 23 '17 at 12:07

Community

1
1

answered Jul 07 '10 at 22:00

fearoffours

1,455
2
12
23

Kieran Allen · Answer 5 · 2010-07-08T09:04:45.353

This isn't the best method.. But if you need to do multiple queries you could use something like...

function multiQuery($sql) 
{
  $query_arr  =  explode(';', $sql);

  foreach ($query_arr as $query)
  {
    mysql_query($query);
  }
}

another example of a helper query

function build_sql_update($table, $data, $where)
{

  $sql  =  '';

  foreach($data as $field => $item)
  {
    $sql .=  "`$table`.`$field` = '".mysql_real_escape_string($item)."',";
  }

  // remove trailing ,
  $sql  =  rtrim($sql, ',');

  return 'UPDATE `' . $table .'` SET '.$sql . ' WHERE ' .$where;
}

echo build_sql_update('cart', array('cart_qty' => 1), 'cart_id=21');

Updating multiple rows in MySQL

5 Answers5

Linked