So much Update query

Question

I am trying to update a table that has more than 400 rows. I am trying to update the entire table! Each and every row need to be updated to a new value. So, I think of doing a loop in php that will generate the required UPDATE statements and fire them to the database one by one.

I feel like that will be harsh to the database and some of them might be lost. Am I right in that? or is it OK to do it this way? and if not, what other way I can do it? Is there a way to make all the update in one query which can be fire once?

Other thought is, creating a big SQL statement that has all the UPDATE statements inside it. But is MySQL support multiply update in one statement?

Thank you in advance.

It's fine to have multiple updates - for an N-statement update make sure to use a *transaction* as appropriate. If there *really is* a problem with multiple updates as so (of which there can be in different situations), then there are other solutions around that, but keep it simple. — user2864740, Jan 22 '14 at 07:52
why not update in one statement? UPDATE `tableName` SET `col` = 'val' WHERE conditions; — cecilozaur, Jan 22 '14 at 07:58

score 3 · Accepted Answer · answered Jan 22 '14 at 08:00

A table with only 400 rows can be considered small. As to performance: it would be best to send just one update statement instead of 400. And this is not what one would call a multiple update as it affects only one table. So no problem. However, depending on what you want to do, your statement may become quite large.

Example 1: Update all records such that col1 is double from what it was before:

update mytable
set col1 = col1 * 2;

Example 2: Update all records with different values:

update mytable
set col1 = 
  case
    when id = 1 then col1 * 2
    when id = 2 then col1 * 3
    when id = 3 then col1 + 18
    when id = 4 then 100
    else null
  end;

That's really what I am looking for. Thank you very much ^^. — Ali Albahrani, Jan 22 '14 at 12:45

score 1 · Answer 2 · edited May 23 '17 at 10:25

1

It is not hard for the database.

If you want to you can chain your update statements like this

mysql_query("update myTable set col1 = 'Row1Val1', col2 = 'Row1Val2' where id = Row1Id;
             update myTable set col1 = 'Row2Val1', col2 = 'Row2Val2' where id = Row2Id;");

but you should always wrap it in a transaction

try {
    $conn->autocommit(FALSE);//starts a transaction
    //this is where you would have a loop or all the queries as one string
    $conn->query($query);
    $conn->commit();
    $conn->autocommit(TRUE); 
}
catch ( Exception $e ) {
    $conn->rollback(); 
    $conn->autocommit(TRUE); //end transaction   
}

For more info look at this question PHP + MySQL transactions examples

edited May 23 '17 at 10:25

Community

1
1

answered Jan 22 '14 at 08:32

Archlight

2,019
2
21
34

That's what I had in mind but I was scared the string will be too long to handle. but +1 for the transaction ID, I like it, thank you ^^. – Ali Albahrani Jan 22 '14 at 12:46
look at this one if you want to see what ppl say about max length of queries in php/mysql http://stackoverflow.com/questions/3026134/is-there-a-limitation-to-the-length-of-the-query-in-mysql – Archlight Jan 22 '14 at 13:25

So much Update query

2 Answers2