How to swap the column values for 2 records in a table?

Question

I'm trying to swap a column value for 2 records in a table. I have a primary key: 'recnr'

 Recnr   File 
 1     img001.jpg 
 2     img002.jpg 
 5     img005.jpg 
 6     img006.jpg

I want the file img005.jpg to move up one place to be:

 Recnr   File 
 1       img001.jpg
 2       img005.jpg
 5       img002.jpg
 6       img006.jpg

How can this be done?

Seems like a bad idea on the surface. Can you provide some background on why you need to do this? — Joe Stefanelli, Feb 18 '11 at 20:10
possible duplicate http://stackoverflow.com/questions/37649/swapping-column-values-in-mysql — Damp, Feb 18 '11 at 20:11
Oh you reformatted the post, I had the question wrong... sorry about that — Damp, Feb 18 '11 at 20:24

bassneck · Answer 1 · 2011-02-18T20:23:33.037

2

You could add an integer field called, for example, weight, and use it with ORDER BY clause.

this would be your original dataset:

Recnr | File        | weight
1     | img001.jpg  | 0
2     | img002.jpg  | 0
5     | img005.jpg  | 0
6     | img006.jpg  | 0

And to get one row higher, you make it weight less

Recnr | File        | weight
1     | img001.jpg  | 0
2     | img002.jpg  | -10
5     | img005.jpg  | 20
6     | img006.jpg  | 0

Then you SELECT * ORDER BY weight ASC to make the lightest records appear at the top. And the result would be

Recnr | File        | weight
2     | img002.jpg  | -10
1     | img001.jpg  | 0
6     | img006.jpg  | 0
5     | img005.jpg  | 20

edited Feb 18 '11 at 20:23

answered Feb 18 '11 at 20:09

bassneck

actually thats what im doing but the problem is how to swap weights? since Recnr is also swapping – Joseph Lafuente Feb 18 '11 at 20:29
I'm afraid I don't quite understand your problem. To change weights use "UPDATE table_name SET weight=-10 WHERE Recnr=2". This will change only the weight of the second row only. – bassneck Feb 18 '11 at 20:39
and how will i swap 2 weights at the same time? maybe 2 and 5 will change weights – Joseph Lafuente Feb 18 '11 at 20:43
@Joseph Lafuente: bassneck's main point is that you should not be using your primary key to sort by - you should have another weight field. For swapping the weight values, ring0's solution works well. – TehShrike Feb 18 '11 at 23:35

score 2 · Answer 2 · answered Feb 18 '11 at 20:35

You can select the two values in two variables, and then update the table:

Set the two variables

SELECT @two:=File FROM mytable WHERE Recnr=2;
SELECT @five:=File FROM mytable WHERE Recnr=5;

Update the table

UPDATE mytable SET File=@five WHERE Recnr=2;
UPDATE mytable SET File=@two  WHERE Recnr=5;

score 1 · Answer 3 · edited May 23 '17 at 10:34

1

By adapting method 3 from the answer to Swapping column values in MySQL I came up with this solution:

UPDATE your_table t1, your_table t2 SET
t1.File = (@temp:=t1.File),
t1.File = t2.File,
t2.File = @temp
WHERE t1.Recnr = 2 AND t2.Recnr = 5;

edited May 23 '17 at 10:34

Community

answered Jun 16 '11 at 18:13

Christian Ammer

3 Answers3