472,133 Members | 1,217 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,133 software developers and data experts.

UPDATE multiple rows in mysql (in one single query)

128 100+

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++){
  3.    $query = "UPDATE table SET column_1 = '$value_column_1[$i]' WHERE
  4.    column_2 = '$value_column_2[$i]'";
  6. }
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. }
  13. is there some thing similar with update?
  15. thank you
Sep 17 '08 #1
0 7960

Post your reply

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

Similar topics

2 posts views Thread by Colin Steadman | last post: by
4 posts views Thread by shank | last post: by
10 posts views Thread by Anthony Robinison | last post: by
3 posts views Thread by spartacus | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.