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
