switching column data in mysql? -

this question has answer here:

• swapping column values in mysql 15 answers

i wanting try , run query update table ptb_messages , switch data of 2 columns around, instance heres table:

id  |  to_user_id  |  from_user_id  |   1         4                5 2         5                6 3         7                9 

so want try , switch value of from_user_id on to_user_id , vice versa.

i going use before realised once copied value 1 table other the original data other column have been overwritten,

$sql = mysql_query("update ptb_messages set ptb_messages.from_user_id=ptb_messages.to_user_id"); 

what need swap function,

im not sure how might im imagining this:

$sql = mysql_query("update ptb_messages set to_user_id=from_user_id, from_user_id=to_user_id"); 

hope can me please.

well, mysql has concept called user variable. can take advantage of store value , set on column swapping,

update table1  set    to_user_id = from_user_id,        from_user_id = @r1  @r1 := to_user_id 

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

how joining table itself?

update  table1         inner join table1 b           on a.to_user_id = b.to_user_id ,               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