Update multiple rows with unique constraint

Question

I have two tables in my database, foo and bar. Multiple foos have one bar, and contain a second column that controls the ordering of foos with the same bar. The values used for ordering should be unique, so the table has a unique constraint:

CREATE TABLE bar (
  id int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);

CREATE TABLE foo (
  id INT NOT NULL AUTO_INCREMENT,
  bar INT,
  `order` INT,
  PRIMARY KEY (id),
  FOREIGN KEY (bar) REFERENCES bar (id),
  UNIQUE KEY (bar, order)
);

What is the most efficient way to update the ordering of the foos for one bar? For example, if I have this data:

id | bar | order
 1 |  1  |   1
 2 |  1  |   2
 3 |  1  |   3
 4 |  1  |   4

And want to reorder them (1, 2, 4, 3), I now have the following queries:

UPDATE foo SET `order` = NULL WHERE id IN (3, 4);
UPDATE foo SET `order` = 3 WHERE id = 4;
UPDATE foo SET `order` = 4 WHERE id = 3;

The first query is necessary to prevent an integrity error for the other updates. Can this be improved?

Just to be clear, are you trying to organise the data inside a table, or assign new values in general — Takarii, Oct 29 '15 at 12:52
I don't care about the actual order in the table, I'm trying to reorder the results when retrieving them by `SELECT ... ORDER BY "order"`. — hackedd, Oct 29 '15 at 13:20
use `order by bar asc, order` and you will first order the data by bar, followed by order — Takarii, Oct 29 '15 at 13:23
The answer you are probably looking for is here:http://stackoverflow.com/questions/11207574/how-to-swap-values-of-two-rows-in-mysql-without-violating-unique-constraint — Sevle, Oct 29 '15 at 13:47
Possible duplicate of [How to swap values of two rows in MySQL without violating unique constraint?](https://stackoverflow.com/questions/11207574/how-to-swap-values-of-two-rows-in-mysql-without-violating-unique-constraint) — mae, Sep 12 '17 at 02:57

score 0 · Answer 1 · answered Oct 29 '15 at 14:03

The only I can think is you should have your update values in a separated table/query to make it more generic and can work with multiple ID

newQuery

 ID   newOrder
 3      4
 4      3

You update your order to null before update because the integrity restriction.

 UPDATE foo SET `order` = NULL WHERE id IN (SELECT ID FROM newQuery);

Then update with a JOIN

UPDATE foo AS f
INNER JOIN newQuery AS n ON f.id = n.id
SET f.order = n.newOrder

Update multiple rows with unique constraint

1 Answers1