CODEWITHSUNDEEP
phpmysql
starleaf1
starleaf1

Reputation: 2866

Updating Multiple MySQL Rows at Once

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?

Upvotes: 3

Views: 1191

Answers (4)

inquam
inquam

Reputation: 12932

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.

Upvotes: 3

Michal Schwarz
Michal Schwarz

Reputation: 72

INSERT INTO ... ON DUPLICATE KEY UPDATE

Table must have a Unique Key. It is faster than single rows updating.

Upvotes: 1

Michael Sivolobov
Michael Sivolobov

Reputation: 13240

You can create another table and INSERT values into it:

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

And then just one update:

UPDATE
    t1
SET
    results = t2.results
INNER JOIN
    t2
ON t1.no = t2.no

Upvotes: 1

duellsy
duellsy

Reputation: 8577

Unfortunately (to my knowledge), you can't do multiple unique updates in a single SQL query.

You can update multiple rows in a single query, but they will each get the same updates applied to them.

Each update needs to be done as its own statement.

Your best bet would be to write a loop that updates a single row, and have it run for each update that needs to be done.

Upvotes: 0

Related Questions