RE: Swap data in columns
ALTER TABLE tablename1 CHANGE column1 column2 VARCHAR(64), CHANGE column2 column1 VARCHAR(64) Assuming the columns are varchar(64) Jangita | +254 76 918383 | MSN Y!: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -Original Message- From: nixofortune [mailto:nixofort...@googlemail.com] Sent: 22 September 2010 6: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
Re: Swap data in columns
Hi Kevin, It works in mysql but not exactly as I need. In my case it copied content of column2 into column1. So, not exactly what I intended to achieve. Thanks. Igor update mydata set column1 = column2, column2 = column1 On Thu, Sep 23, 2010 at 12:03 AM, Kevin (Gmail) kfoneil...@gmail.comwrote: update mydata set column1 = column2, column2 = column1 (works in sqlserver, can't try mysql at the moment) You can select which rows by adding a where clause obviously. I suppose that the field values are copied to a buffer which is the written to the table at the end of the update (or row by row?) - Original Message - From: nixofortune nixofort...@googlemail.com To: mysql@lists.mysql.com Sent: Wednesday, September 22, 2010 5:29 PM 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
RE: Swap data in columns
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
Re: Swap data in columns
Couldn't you just rename the columns?
JW
On Wed, Sep 22, 2010 at 11:53 AM, Rolando Edwards
redwa...@logicworks.netwrote:
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
--
-
Johnny Withers
601.209.4985
joh...@pixelated.net
Re: Swap data in columns
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.netwrote:
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
Re: Swap data in columns
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 :
rolandologicworxredwa...@logicworks.nethttp://www.linkedin.com/in/rolandoedwards
-Original Message-
From: nixofortune [mailto:nixofort...@googlemail.com
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
Re: Swap data in columns
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
Re: Swap data in columns
This is even better!
JW
On Wed, Sep 22, 2010 at 2:27 PM, Steve Staples sstap...@mnsi.net 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
redwa...@logicworks.netwrote:
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=joh...@pixelated.net
--
-
Johnny Withers
601.209.4985
joh...@pixelated.net
RE: Swap data in columns
Oh yea, INNER JOIN is cleaner to use
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: Egor Shevtsov [mailto:nixofort...@googlemail.com]
Sent: Wednesday, September 22, 2010 4:14 PM
To: MySQL mailing list
Subject: Re: Swap data in columns
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=redwa...@logicworks.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Swap data in columns
update mydata set column1 = column2, column2 = column1 (works in sqlserver, can't try mysql at the moment) You can select which rows by adding a where clause obviously. I suppose that the field values are copied to a buffer which is the written to the table at the end of the update (or row by row?) - Original Message - From: nixofortune nixofort...@googlemail.com To: mysql@lists.mysql.com Sent: Wednesday, September 22, 2010 5:29 PM 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
