SQL swap field values

Question

I have a SQL table (MYSQL 4.0 to be precise) composed of the following :

int no (primary)
int field1
int field2

I would like to swap the values of field1 with the values of field2 and vice-versa.

Ex.: 1;2.5;3.76 becomes 1;3.76;2.5

I need a swapping temporary variable of some sort. But I don't think I can use something like

Set @var1 = field1

inside my update statement. And I don't think altering the table to add a temporary column is my best bet either.

Answer 1

My initial thought was to try this:

UPDATE YourTable
   SET field1=field2
      ,field2=field1
   WHERE ...

SQL Server syntax here (but has worked this way in every database I've ever used, I've not used mysql):

declare @x table (field1 int, field2 int)
insert @x values (1,2)
update @x set field1=field2, field2=field1
select * from @x

OUTPUT

field1      field2
----------- -----------
2           1

(1 row(s) affected)

However after a little research, I came across this: http://stackoverflow.com/questions/37649/swapping-column-values-in-mysql/559291#559291

Answer 2

How about creating a work table, push the data there and the join with your current table?

Answer 3

I found this

UPDATE swap_test 
SET x=(@temp:=x), 
    x = y, 
    y = @temp
WHERE ...

here as solution with session variables (there are other solutions in the provided link and also a link back to another so question)