List:General Discussion« Previous MessageNext Message »
From:Steve Staples Date:September 22 2010 7:27pm
Subject:Re: Swap data in columns
View as plain text  
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
> <redwards@stripped>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
> > redwards@stripped
> > http://www.linkedin.com/in/rolandoedwards
> >
> >
> > -----Original Message-----
> > From: nixofortune [mailto:nixofortune@stripped]
> > Sent: Wednesday, September 22, 2010 12:30 PM
> > To: mysql@stripped
> > 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=1
> >
> >
> 
> 


Thread
Swap data in columnsnixofortune22 Sep
  • RE: Swap data in columnsRolando Edwards22 Sep
    • Re: Swap data in columnsJohnny Withers22 Sep
      • Re: Swap data in columnsSteve Staples22 Sep
        • Re: Swap data in columnsJohnny Withers22 Sep
    • Re: Swap data in columnsEgor Shevtsov22 Sep
      • RE: Swap data in columnsRolando Edwards22 Sep
  • Re: Swap data in columnsKevin \(Gmail\)23 Sep
    • Re: Swap data in columnsnixofortune23 Sep
  • RE: Swap data in columnsJangita23 Sep
Re: Swap data in columnsnixofortune22 Sep