List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:September 22 2010 4:53pm
Subject:RE: Swap data in columns
View as plain text  
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
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