switching column data in mysql?

Question

i am wanting to try and run a query which will update my table ptb_messages and switch the data of two columns around, for instance heres my table:

id  |  to_user_id  |  from_user_id  | 

1         4                5
2         5                6
3         7                9

so what i want to try and do is switch the value of from_user_id over to to_user_id and vice versa.

i was going to use this before i realised that once i copied the value from one table to the other the the original data from the other column would then have been overwritten,

$sql = mysql_query("UPDATE ptb_messages SET ptb_messages.from_user_id=ptb_messages.to_user_id");

what i really need is a swap function,

im not really sure how i might do this but im imagining its something like this:

$sql = mysql_query("UPDATE ptb_messages SET to_user_id=from_user_id, from_user_id=to_user_id");

hope someone can help me please.

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Kermit, Apr 09 '13 at 02:08
Take a look at this -->http://stackoverflow.com/questions/37649/swapping-column-values-in-mysql — xQbert, Apr 09 '13 at 02:11

Skinny Pipes · Answer 1 · 2013-04-09T02:34:41.720

2

Well, mysql has a concept called user variable. You can take advantage of this to store the value and set it on the column for swapping,

UPDATE Table1 
SET    to_user_id = from_user_id,
       from_user_id = @r1
WHERE  @r1 := to_user_id

see here: http://www.sqlfiddle.com/#!2/8cd6a/1

how about joining the table with itself?

UPDATE  Table1 a
        INNER JOIN Table1 b
          ON a.to_user_id = b.to_user_id AND
              a.from_user_id = b.from_user_id
SET     a.to_user_id = b.from_user_id,
        a.from_user_id = b.to_user_id

see here: http://www.sqlfiddle.com/#!2/d6b4f/1

edited Apr 09 '13 at 02:34

answered Apr 09 '13 at 02:18

Skinny Pipes

1,025
6
14

that still gives me the same problem though, its updating the from_user_id and copying it into the to_user_id but the to_user_id is not being copied to the from_user_id :/ – Jack Harper Apr 09 '13 at 02:29
You have absolutely no guarantee that to_user_id will be updated before from_user_id if I am not mistaken. – Lajos Arpad Apr 09 '13 at 02:31
i can't understand. It seems to work on fiddle right? – Skinny Pipes Apr 09 '13 at 02:31
Ok, your update looks a good one to me, +1 and cheers. – Lajos Arpad Apr 09 '13 at 02:38

score 1 · Answer 2 · answered Apr 09 '13 at 02:25

I'm going to throw an answer out there, although I think Skinny Pipes answer is a little more elegant.

You can create a temporary table to store the values in.

http://www.sqlfiddle.com/#!2/3631d/1

create table ptb_messages_temp (
  id int, 
  tempid int);

insert into ptb_messages_temp (id, tempid) (select id, to_user_id from ptb_messages);

-- DO THE UPDATE
UPDATE ptb_messages 
LEFT OUTER JOIN ptb_messages_temp on ptb_messages.id=ptb_messages_temp.id
SET  
ptb_messages.to_user_id=ptb_messages.from_user_id, 
ptb_messages.from_user_id=ptb_messages_temp.tempid;

switching column data in mysql?

2 Answers2