What about: select `id`, `column1` as 'column2', `column2` as 'column1'; Steve
On Wed, 2010-09-22 at 13:06 -0500, Johnny Withers wrote: > 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 > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org