Couldn't you just rename the columns? JW
On Wed, Sep 22, 2010 at 11:53 AM, Rolando Edwards <redwa...@logicworks.net>wrote: > I ran these commands: > > use test > DROP TABLE IF EXISTS mydata; > CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1 > CHAR(1),column2 CHAR(2)); > INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'), > ('c','x'), ('d','w'), ('e','v'); > SELECT * FROM mydata; > UPDATE mydata A LEFT JOIN mydata B USING (id) SET > A.column1=B.column2,A.column2=B.column1; > SELECT * FROM mydata; > > I got this output: > > lwdba@ (DB test) :: use test > Database changed > lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata; > Query OK, 0 rows affected (0.00 sec) > > lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT > PRIMARY KEY,column1 CHAR(1),column2 CHAR(2)); > Query OK, 0 rows affected (0.05 sec) > > lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES ('a','z'), > ('b','y'), ('c','x'), ('d','w'), ('e','v'); > Query OK, 5 rows affected (0.00 sec) > Records: 5 Duplicates: 0 Warnings: 0 > > lwdba@ (DB test) :: SELECT * FROM mydata; > +----+---------+---------+ > | id | column1 | column2 | > +----+---------+---------+ > | 1 | a | z | > | 2 | b | y | > | 3 | c | x | > | 4 | d | w | > | 5 | e | v | > +----+---------+---------+ > 5 rows in set (0.00 sec) > > lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET > A.column1=B.column2,A.column2=B.column1; > Query OK, 5 rows affected (0.03 sec) > Rows matched: 5 Changed: 5 Warnings: 0 > > lwdba@ (DB test) :: SELECT * FROM mydata; > +----+---------+---------+ > | id | column1 | column2 | > +----+---------+---------+ > | 1 | z | a | > | 2 | y | b | > | 3 | x | c | > | 4 | w | d | > | 5 | v | e | > +----+---------+---------+ > 5 rows in set (0.00 sec) > > GIVE IT A TRY !!! > > Rolando A. Edwards > MySQL DBA (CMDBA) > > 155 Avenue of the Americas, Fifth Floor > New York, NY 10013 > 212-625-5307 (Work) > AIM & Skype : RolandoLogicWorx > redwa...@logicworks.net > http://www.linkedin.com/in/rolandoedwards > > > -----Original Message----- > From: nixofortune [mailto:nixofort...@googlemail.com] > Sent: Wednesday, September 22, 2010 12:30 PM > To: mysql@lists.mysql.com > Subject: Swap data in columns > > Hi all. > > Sorry for very simple question, just can't figure out the solution. > I need to swap data in column1 with data in column2. > > > +----+---------+---------+ > | id | column1 | column2 | > +----+---------+---------+ > | 1 | a | z | > | 2 | b | y | > | 3 | c | x | > | 4 | d | w | > | 5 | e | v | > +----+---------+---------+ > > Can you achieve this with a simple query? > so for id 1 column1 = 'z' and column2 = 'a' and so on. > > Thanks guys, > Igor > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net