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 :
> [email protected]http://www.linkedin.com/in/rolandoedwards
>
>
> -----Original Message-----
> From: nixofortune [mailto:[email protected]
> <[email protected]>]
> Sent: Wednesday, September 22, 2010 12:30 PM
> To: [email protected]
> 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
>
>
>
>
