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.

Do you need to do anything other than UPDATE YourTable SET field1=field2, field2=field1 — mdma, May 21 '10 at 14:43
I'm assuming you're doing this to only a subset of the columns? (Otherwise, you could just rename the columns!) A temporary column seems the easiest, and most straightforward solution to me. — wlangstroth, May 21 '10 at 14:52
@mdma : Yes, I need to do something else: it doesn't work. Btw, your comment should be an answer, not a comment. — Philippe Carriere, May 21 '10 at 15:11
@Will : I do need to apply this only to a subset of rows but I like your way to think outside the box. Would have upvoted for that reason if this had been an answer instead of a comment. — Philippe Carriere, May 21 '10 at 15:12

score 2 · Answer 1 · edited May 23 '17 at 12:30

2

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: Swapping column values in MySQL

edited May 23 '17 at 12:30

Community

answered May 21 '10 at 14:43

KM.

This will not work as expected in mysql, there's a overview of UPDATE behaviours in one of the so questions, mysql and ather DBMS fail on this – Unreason May 21 '10 at 14:47
@Unreason, works fine in SQL Srver, but see the link in my edited answer. – KM. May 21 '10 at 14:55
@KM, It works in oracle, postgres, db2 and I think SQLite as expected. It was mysql and another one failing... can't find the question now. – Unreason May 21 '10 at 15:05
Ok, found it http://stackoverflow.com/questions/2203202/sql-update-order-of-evaluation – Unreason May 21 '10 at 15:08
@Silence said `After testing it: doesn't work in mysql`. Duh, I said that the code was for SQL Server! The linked answer shows how to do this in mysql. – KM. May 21 '10 at 15:40

score 2 · Answer 2 · answered May 21 '10 at 14:44

2

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

answered May 21 '10 at 14:44

Raj

Unreason · Accepted Answer · 2010-05-21T15:21:14.540

2

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)

edited May 21 '10 at 15:21

answered May 21 '10 at 14:57

Unreason

@Silence, yes you are right, no point in keeping the multi table version of the answer, the documentation at http://dev.mysql.com/doc/refman/5.1/en/update.html clearly states that the behaviour is not defined (although I'll run some tests now out of curiosity) – Unreason May 21 '10 at 15:22
Works. Easiest answer to implement. Thank you. – Philippe Carriere May 21 '10 at 15:23

3 Answers3