MySQL swap column values of two rows without violating the unique contraint?

Question

Similar to How to swap values of two rows in MySQL without violating unique constraint?, but not quite..

I have a table like this:

CREATE TABLE IF NOT EXISTS `links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order` int(11) NOT NULL,
  `text` varchar(31) NOT NULL,
  `html_text` varchar(63) NOT NULL,
  `link` varchar(127) NOT NULL,
  `html_link` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order` (`order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

It represents links in a navigation area of a website. E.g.: a link to the homepage, a link to the contact page, etc. I want to be able to change the order of these links, that's why I have the column order. The values of this column are unique, so I can sort by order.

Now I want to swap the positions of two links in this ordered list, so I want to swap the values of order. But unlike the other question, I do not know the value's of the order-fields, I only know the id values of the two rows I want to swap. I'd like to have only one query.

Can anybody help me with this, as I'm not very experienced with complex MySQL queries.

score 4 · Accepted Answer · answered Jun 06 '13 at 19:07

4

There is no straight forward logic for this in a single query.

Conside you want to swap x,y

You have to update first x with a different value z. (Some logic to arrive at a very unique value is required here).
Then update y as x
Then update z as y

answered Jun 06 '13 at 19:07

RGV

732
3
10

So 3 update queries is the only way to go, assuming I know z in advance? (for instance, I know -1 is never used in my table) – Jochem Kuijpers Jun 06 '13 at 19:12
1

Yes. That's the best way! I know. – RGV Jun 06 '13 at 19:12
Ok thanks! I'll approve this since it's most likely the best answer I'll get on this. – Jochem Kuijpers Jun 06 '13 at 19:13
Minimum of 2 updates are needed but Rajaganesh is correct. You can't do this with 1 update in MySQL, unfortunately. – ypercubeᵀᴹ Jun 06 '13 at 19:52
You are totally wrong! http://stackoverflow.com/questions/4517219/how-to-update-swap-values-of-two-rows-with-single-query – Jacek Kowalewski Jul 10 '14 at 07:57
1

@JacekKowalewski That answer doesn't work with a Unique Index on the columns. – 0b10011 Nov 21 '14 at 19:05

MySQL swap column values of two rows without violating the unique contraint?

1 Answers1