List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:September 22 2010 8:54pm
Subject:RE: Swap data in columns
View as plain text  
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
redwards@stripped
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: Egor Shevtsov [mailto:nixofortune@stripped] 
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
> 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

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