3

I have a table sites and basically a travelling salesman problem. My boss wants to select a bunch of sites out of the list, then sort them manually into a visit order. I have looked for similar questions, but they were not targeted at MySQL, and those that were didn't provide a reasonable solution for my situation. I didn't do Computer Science at university, so hopefully this is bread-and-butter stuff for some of you out there.

I would like to do something like the following pseudo code:

UPDATE sites SET run_order=0 WHERE selected='false';
UPDATE sites SET run_order=AUTO_SEQUENCE(DESC FROM 6) WHERE site_id=SEQUENCE(23,17,9,44,2,14);

The latter of those would have the same effect as:

UPDATE sites SET run_order=6 WHERE site_id=23;
UPDATE sites SET run_order=5 WHERE site_id=17;
UPDATE sites SET run_order=4 WHERE site_id=9;
UPDATE sites SET run_order=3 WHERE site_id=44;
UPDATE sites SET run_order=2 WHERE site_id=2;
UPDATE sites SET run_order=1 WHERE site_id=14;

Since I am running this via PHP, I don't want to have to issue many individual queries, even though the number of sites my boss could visit in a day is of course limited by the internal combustion engine.
My SQL table looks like this:

+---------------+----------------------+------+-----+---------+----------------+
| Field         | Type                 | Null | Key | Default | Extra          |
+---------------+----------------------+------+-----+---------+----------------+
| site_id       | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| ...           |                      |      |     |         |                |
| selected      | enum('false','true') | NO   |     | false   |                |
| run_order     | int(10) unsigned     | NO   |     | 0       |                |
+---------------+----------------------+------+-----+---------+----------------+
Community
  • 1
  • 1
Nicholas Shanks
  • 10,623
  • 4
  • 56
  • 80
  • Whats wrong with [this answer](http://stackoverflow.com/questions/3882388/updating-multiple-rows-with-different-values)? It is for MySQL and only one statement. – Jacob Jul 18 '11 at 08:02
  • The accepted answer on that page, the `CASE WHEN ... THEN ...` syntax would work for me but is not automatic. I would have to compute each THEN value in a PHP loop, rather that farm that off to the MySQL server. I was looking for a more succinct syntax. – Nicholas Shanks Jul 18 '11 at 10:34

1 Answers1

0

I think this is the code you are looking for.

http://www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/

Matt
  • 3,778
  • 2
  • 28
  • 32
  • Thanks Matt, but that is not automatic, and appears to be the same as the first question I linked to in my own question. – Nicholas Shanks Jul 18 '11 at 10:41
  • Read to the end, the difference is they show how you can use php to generate a single sql statement to do all the updates. The only other way I can think of is to create a temporary table and join on that in the update, but this way seems much simpler and therefore better. – Matt Jul 18 '11 at 11:50