Updating Multiple MySQL Rows at Once

Question

Here's the scenario:

When the customer first place his/her order, the table is empty; looks like this

+----+------+---------+
| no | item | results |
+----+------+---------+

When the order has been placed, the table looks like this

+----+--------+---------+
| no | item   | results |
+----+--------+---------+
| 1  | Test 1 | null    |
| 2  | Test 2 | null    |
| 3  | Test 3 | null    |
+----+--------+---------+

That's the easy part. I can use comma separated VALUE clauses in the INSERT query.

The order is then sent to the lab, and when the lab guys are done with the job, the table becomes like this:

+----+--------+---------+
| no | item   | results |
+----+--------+---------+
| 1  | Test 1 | Res 1   |
| 2  | Test 2 | Res 2   |
| 3  | Test 3 | Res 3   |
+----+--------+---------+

This is where your answers come in. I need to update the tests with the results. However I'd really prefer not to do dozens of UPDATE--SET--WHERE statements, one for each test items. Is there any other way?

Answer 1

Depending on storage engine restrictions etc you could use insert on duplicate

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

This allows you to use a insert query that updates values if the rows already exist.

Something like

insert into table (no, item, result) values(1, "A", "B") on duplicate key update item=values(item), result=values(result)

There is also REPLACE INTO.

http://dev.mysql.com/doc/refman/5.0/en/replace.html

One of these should fit your need.