• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5279
  • Last Modified:

updating multiple rows at once

Scenario:

I query the db and I get a number of rows returned.

They are displayed in a form with fields to be updated

I know how to do the update on a one by one basis.

What I want to do is be able to update multiple rows at once.

Let say for example 5 fields per row and 10 rows.

There is something about arrays and loop thru it that I cannot get to work.

Any help would be greatly appreciated.

Gamebits
0
gamebits
Asked:
gamebits
1 Solution
 
todd_farmerCommented:
UPDATE mytable SET user_state='CA' WHERE zipcode between 92000 AND 95000;

would udate all rows in the table that match the criteria.  If you are wanting to update records to different values, you need to do this at the application layer (or possibly in a stored procedure with MySQL 5.0).
0
 
NopiusCommented:
I aggree with todd_farmer, but have some more explaination.

'I query the db and I get a number of rows returned' - it's your starting point.
You already have some query statement like: SELECT * FROM lala WHERE lalala;
Just change 'SELECT * FROM lala' to 'UPDATE lala', then append before WHERE: 'SET col1=val1, col2=val2, ... '
and you will get SQL statement, that you asked for.
0
 
Yuval_ShohatCommented:
In addition to todd_farmer's and Nopius' comments, you can also update according to results of a subquery:
update t1 set t='zzzz' where id in (select id from t2)

this query for example, will update t column in t1 table where the t1.id is found in the subquery (select id from t2).
every row that has an id in t1 which is equal to any id in t2 will be updated.

Yuval.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
NelliosCommented:
When I want to update records to different values, I usally have a table that holds the update criteria & values and combine the two tables with join.
As an example I will use the same example with todd.

I would have a table (matchtable) that would hold zipcodes/states.

UPDATE
mytable
left join match_table on(matchtable.zipcodemax > mytable.zipcode && matchtable.zipcodemin < mytable.zipcode)
SET mytable.user_state=match_table.stat
WHERE match_table.state IS NOT NULL;

Of course this approach doesnt serve all cases, but it is quite good, when u want to avoid modifyinh your application layer or you are not using mysql 5 or later.
0
 
gamebitsAuthor Commented:
I think I need to clarify my question, I'll go with a example, but I think Todd might be right it's more at the application level (php) than at the mysql level that I will find the answer, anyway here it goes.

1) I query the db which return 5 rows

checkbox(id1), field1, field2, field 3, field4, field5
checkbox(id2), field1, field2, field 3, field4, field5
checkbox(id3), field1, field2, field 3, field4, field5
checkbox(id4), field1, field2, field 3, field4, field5
checkbox(id5), field1, field2, field 3, field4, field5

as you can see I have checkbox setup so I can choose which record to update and query the db again to populate the update form. Lets make it simple I will choose id 1 and 3 so the next page I have the form displayed twice populated with the appropriate data according to the id.

ID1
field1: input box with data1
field2: input box with data2
field3: input box with data3
field4: input box with data4
field5: input box with data5

ID3
field1: input box with data1
field2: input box with data2
field3: input box with data3
field4: input box with data4
field5: input box with data5

button submit value=edit

Now I'm able to edit the data in the forms and it the submit button. If there was only one form to update one row I wouldn't have any problem but since I have 2 forms to edit/update 2 rows I hust cannot figure it out.

And my example has only 5 fields and i want to update 2 rows but in real life there is about 160 ids and up to 14 fields to edit for each one of them.

Thanks

Gamebits

0
 
gamebitsAuthor Commented:
Thanks guys but Todd was right, for the query to update multiple rows with different value it had to be done at the application level, (I do not run mysql 5.0).

Thanks

Gamebits
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now