Hi Rolando,
This is perfect solution I was looking for.
Why do you use left join here? It looks like inner join works fine as well.
Thanks.
Rolando Edwards 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=arch...@jab.org
