updating many fields in same table...

[leelong]

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."";

[r937]

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

[leelong]

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

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?

[oddz]

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)

[leelong]

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)

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.

[leelong]

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';
.
.
.

[oddz]

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

[leelong]

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

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.

[oddz]

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…

[SpacePhoenix]

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