PHP Mysql update multiple columns with multiple row values by single query

Question

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?

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
– jeroen, Commented Mar 4, 2015 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
– jean, Commented Mar 4, 2015 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
– Be Upen, Commented Mar 4, 2015 at 13:27

Shravan Sharma · Accepted Answer · 2015-03-04 13:38:10Z

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;
}

answered Mar 4, 2015 at 13:38

Shravan Sharma

9898 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jordy · Accepted Answer · 2015-03-04 13:16:37Z

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'

answered Mar 4, 2015 at 13:16

Jordy

1,0692 gold badges11 silver badges29 bronze badges

Comments

risyasin · Accepted Answer · 2015-03-04 13:18:27Z

-1

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

update table set column1='100',column2='140,column3='150' where id IN (1,2,3)

answered Mar 4, 2015 at 13:18

risyasin

1,32114 silver badges24 bronze badges

3 Comments

jeroen Over a year ago

The values are not the same, check the question (okay, the formatting does not help...).

Be Upen Over a year ago

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

risyasin Over a year ago

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.

Collectives™ on Stack Overflow

PHP Mysql update multiple columns with multiple row values by single query

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related