Updating many fields in same table

Databases
1

say i have one table with several fields, and want to update one field many times with different values



UPDATE sample_table SET field_one = '$val_one' WHERE member = '$member_one' AND id = '$id';

UPDATE sample_table SET field_one = '$val_two' WHERE member = '$member_two' AND id = '$id';

UPDATE sample_table SET field_one = '$val_three' WHERE member = '$member_three' AND id = '$id';

.
.
.

I am excuting the UPDATE operation inside a loop, so I need to figure out how to do it in just one query.

I know how to do it with INSERT.



for($i=0; $i < 10; $i++){

  $val = ...;
  $member = ...;

  $comma = ($i == 9)?'':',';
  
  $insert .= "('$val','$member','$id')".$comma;

}

$q_insert = "INSERT INTO sample_table VALUES".$insert."";
2

the problem is not that you are updating many columns in the same row, but the same column in many rows

go ahead and use separate UPDATEs for separate rows

3

did u mean i must UPDATE many times ONE by ONE?

for($i=0; $i < 10; $i++){
 
  $val = ...;
  $member = ...;
 
  mysql_query("UPDATE sample_table SET field_one = '$vale' WHERE member = '$member' AND id = '$id'");

}

there are 10 queries here…mean 10 round trip to DB?

may be I can DELETE all the rows with id = ‘$id’, then insert like i mentioned.

so there will be only 2 queries, DELETE and INSERT.

i will get the same thing afterall.

your advice?

4

If a unique key exists on member and id you can achieve what your trying to accomplish using an insert with a duplicate key update in a single query.


INSERT IGNORE INTO sample_table (member,id,field_one) VALUES ('member_one',1,'val_one'),('member_two',2,'val_two'),('member_three',3,'val_three') ON DUPLICATE KEY UPDATE field_one = VALUES(field_one)
5

thank you very much! will try that out. deleting and inserting again is not an option since my real world table has many fields with datas that must be retained.

6

sorry that doesn’t work in my case. look like it simply doing what INSERT does.

these are the 2 fields that identify which row to update, where field id has the same value – $id

WHERE member = ‘$member_one’ AND id = ‘$id’;
WHERE member = ‘$member_two’ AND id = ‘$id’;
.
.
.

7

What column(s) on said table is a primary or unique key?

8

sorry that table has no primary/unique key but field id as index only

CREATE table sample_table (
field_one…
field_two…
field_three…
member…
id…
.
.
.
INDEX(id))TYPE=MyISAM"

it is not possible to create unique, even primary key i think.

9

Then there is no other way to achieve what you want without multiple queries. Why isn’t id a primary key? You should have a primary key defined on table…

10

Assuming the ID field is an auto-increment why not make it the primary key?