This is even better!
JW
On Wed, Sep 22, 2010 at 2:27 PM, Steve Staples <sstaples@stripped> wrote:
> 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
> > >
> > >
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
--
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped
