By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,637 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

UPDATE multiple rows in mysql (in one single query)

bilibytes
100+
P: 128
hi,

i am trying to UPDATE multiple rows with mysql. I know how to do it with multiple queries but i think it would be less resource consuming generating mysql query code with php and update all one single step.

here is the method i usually employ:

$value_column_1 = array();
$value_column_2 = array();
Expand|Select|Wrap|Line Numbers
  1. for($i = 0; $i<= $number_of_updates; $i++){
  2.  
  3.    $query = "UPDATE table SET column_1 = '$value_column_1[$i]' WHERE
  4.    column_2 = '$value_column_2[$i]'";
  5.  
  6. }
  7.  
then i make the query and it works properly,
but i think it would be more efficient to make a query like the ones that can be made with
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO table(column_1, column_2) VALUES ($value_column_1[$i], $value_column_2[$i])
...
making a "big" query:
Expand|Select|Wrap|Line Numbers
  1. $first_time = true; //first time is set to determine weather to put a ', ' or not 
  2. $q = "INSERT INTO table(column_1, column_2) VALUES";
  3. for($i = 0; $i<= $number_of_updates; $i++){
  4.    if($first_time == true){
  5.       $q = $q."($value_column_1[$i], $value_column_2[$i])";
  6.       $first_time = false;
  7.    }
  8.    else{
  9.       $q = $q.",($value_column_1[$i], $value_column_2[$i])";
  10.    }
  11. }
  12.  
  13. is there some thing similar with update?
  14.  
  15. thank you
  16.  
Sep 17 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.