0

I am trying to update rows in mysql, but I have to use a "for" loop for multiple update queries, which have multiple values in each query. The queries are like the following:

update table set column1='100',column2='140,column3='150' where id =1
update table set column1='120',column2='145,column3='154' where id =2
update table set column1='141',column2='148,column3='155' where id =3

I am using a "for" loop to run multiple queries with different id's, I want to run a single query to update all rows, but not by using "case". Is that possible?

markkus
  • 51
  • 2
  • 6
Be Upen
  • 3
  • 1
  • 2
  • As the rows and data do not seem to be related, I would probably prepare a query once and execute it multiple times with different data sets. – jeroen Mar 04 '15 at 13:17
  • see http://stackoverflow.com/questions/25674737/mysql-update-multiple-rows-with-different-values-in-one-query – Paul Lo Mar 04 '15 at 13:17
  • Also you can issue multiple commands ina single batch, that means if you are creatting your script on the fly (and avoiding SQL injects attacks) you can prepare a single script with many updates as you need – jean Mar 04 '15 at 13:22
  • Sorry, i have multiple values for multiple rows. The column name is static, but values are dynamic. update table set column1='100',column2='140,column3='150' where id =1 update table set column1='120',column2='145,column3='154' where id =2 update table set column1='141',column2='148,column3='155' where id =3 – Be Upen Mar 04 '15 at 13:27

3 Answers3

1

you can use loop for generating dynamic query. please have a look. This might be helpful to you.

$data[] = array("column1"=>100, "column2"=>140, "column3"=>150, "id"=>1 );
$data[] = array("column1"=>120, "column2"=>145, "column3"=>154, "id"=>2 );
$data[] = array("column1"=>142, "column2"=>148, "column3"=>155, "id"=>3 );

foreach($data as $dat){
    $query = "UPDATE table SET column1=".$dat['column1'].", column2=".$dat['column2'].", column3=".$da['column2']." WHERE id=".$dat['id'];
    echo $query;
}
Shravan Sharma
  • 989
  • 8
  • 17
0

Why would you put the WHERE clause if you wanna update all the ID's? If you don't wanna update all the ID's you will have to do it in a for loop.

UPDATE tableName SET column1 = '100', column2 = '140', column3 = '150'
Jordy
  • 948
  • 2
  • 9
  • 28
0

You can use WHERE IN(...) for this. Eg:

update table set column1='100',column2='140,column3='150' where id IN (1,2,3)
risyasin
  • 1,325
  • 14
  • 24
  • The values are not the same, check the question (okay, the formatting does not help...). – jeroen Mar 04 '15 at 13:19
  • Sorry, i have multiple values for multiple rows. The column name is static, but values are dynamic. update table set column1='100',column2='140,column3='150' where id =1 update table set column1='120',column2='145,column3='154' where id =2 update table set column1='141',column2='148,column3='155' where id =3 – Be Upen Mar 04 '15 at 13:20
  • If you have "multiple" values. then you have to use "multiple" queries since php's mysql_query() supports only one "write" query at a time. – risyasin Mar 04 '15 at 13:24